Creating a dynamic linq query

Question

I have the following query:

from p in dataContext.Repository<IPerson>() join spp1 in dataContext.Repository<ISportsPerPerson>() on p.Id equals spp1.PersonId join s1 in dataContext.Repository<ISports>() on spp1.SportsId equals s1.Id join spp2 in dataContext.Repository<ISportsPerPerson>() on p.Id equals spp2.PersonId join s2 in dataContext.Repository<ISports>() on spp2.SportsId equals s2.Id where s1.Name == "Soccer" && s2.Name == "Tennis" select new { p.Id }; 

He chooses everyone who plays soccer and tennis.
At run time, the user can select other tags to add to the request, for example: "Hockey". Now my question is: how can I dynamically add Hockey to the request? If Hockey is added to the request, it will look like this:

 from p in dataContext.Repository<IPerson>() join spp1 in dataContext.Repository<ISportsPerPerson>() on p.Id equals spp1.PersonId join s1 in dataContext.Repository<ISports>() on spp1.SportsId equals s1.Id join spp2 in dataContext.Repository<ISportsPerPerson>() on p.Id equals spp2.PersonId join s2 in dataContext.Repository<ISports>() on spp2.SportsId equals s2.Id join spp3 in dataContext.Repository<ISportsPerPerson>() on p.Id equals spp3.PersonId join s3 in dataContext.Repository<ISports>() on spp3.SportsId equals s3.Id where s1.Name == "Soccer" && s2.Name == "Tennis" && s3.Name == "Hockey" select new { p.Id }; 

It would be preferable if the query would dynamically grow like this:

 private void queryTagBuilder(List<string> tags) { IDataContext dataContext = new LinqToSqlContext(new L2S.DataContext()); foreach(string tag in tags) { //Build the query? } } 

Does anyone have an idea how to properly set this up? Thanks in advance!

Answer 1

My colleague and I found a solution, and we reorganized the request for it to work correctly. Now we use the following query to get the correct result set:

 var query = dataContext.Repository<ILead>(); foreach (var tag in tags) { String tagName = tag; query = query.Where(l => dataContext.Repository<ISportsPerPerson>() .Any(tpl => tpl.PersonId.Equals(l.Id) && tpl.Sports.Name.Equals(tagName))); } // Do something with query resultset :] 

Answer 2

The LINQ query is not processed until it is completed. So you can do things like this:

 var q = from r in ctx.records /* Do other stuff */ select r; if (!string.IsNullOrEmpty(search)) { q = from r in q where r.title == search select r; } if (orderByName) { q = q.OrderBy(r => r.name); } /* etc */ 

this will create one executable SQL statement.

For your specific question: joins make this a bit complicated, but I think you can join other “dynamic” queries.

So, you would end up with something like this:

 var baseQ = from p in dataContext.Repository<IPerson>() select p; foreach(var tag in tags) { baseQ = from p in baseQ join spp1 in dataContext.Repository<ISportsPerPerson>() on p.Id equals spp1.PersonId join s1 in dataContext.Repository<ISports>() on spp1.SportsId equals s1.Id where s1.name == tag select p; } /* If you have defined your relations correct, simplify to something like this. Does not actually work because of SportsPerPerson probably has multiple sports: */ foreach(var tag in tags) { baseQ = baseQ.Any(p => p.SportsPerPerson.Sports.Name == tag); } var resultQ = from p in baseQ select new { p.Id };