CODEWITHSUNDEEP
c#linqlinq-to-sql
Dan.
Dan.

Reputation: 717

Concatenating a LINQ (To SQL) Query

I am building a LINQ query, which will have comparisons attached to the 'where' section (the number of these comparisons depends on the user's selections).

In the code-behind, I want something like this:

var builtQuery =
             from q in dc.Leads
             join sr in dc.SalesReps on q.SalesRepID equals sr.SalesRepID
             join co in dc.Companies on q.CompanyID equals co.CompanyID
             join or in dc.Origins on q.OriginID equals or.OriginID
             join pr in dc.Products on q.ProductID equals pr.ProductID
             where

Here, in between the 'from' and 'select' parts, I will add a number of comparisons (depending on the user's selection of checkboxes).

And Finally:

select new { q.Ref, sr.Rep, q.Deposit, q.Sale, q.Title, q.Names, q.Surname, q.HomePhone, q.WorkPhone, q.Mobile, q.Address, q.Suburb, q.County, q.Postcode, co.CompanyName, or.OriginName, pr.ProductName, q.Telemarket, q.Entered };

In PHP (using MySQL) I could simply concatenate a number of strings, which make up the query. But, in c#/LINQ To SQL, the query is not a string and so I have no idea how to do this...There were a couple similar questions on SO, but they're not quite the same thing.

Any ideas??

Thanks!

Upvotes: 0

Views: 277

Answers (1)

Disappointed
Disappointed

Reputation: 1120

I would do it in the following way

var intermediateQuery=
         from q in dc.Leads
         join sr in dc.SalesReps on q.SalesRepID equals sr.SalesRepID
         join co in dc.Companies on q.CompanyID equals co.CompanyID
         join or in dc.Origins on q.OriginID equals or.OriginID
         join pr in dc.Products on q.ProductID equals pr.ProductID

 select new { q.Ref, sr.Rep, q.Deposit, q.Sale, q.Title, q.Names, q.Surname,             q.HomePhone, q.WorkPhone, q.Mobile, q.Address, q.Suburb, q.County, q.Postcode, co.CompanyName, or.OriginName, pr.ProductName, q.Telemarket, q.Entered };

and then add some filters considering user input

if(SomeUserProductFilter)
  {
     var result = intermediateQuery.Where(p=>p.ProductName = 'UserProductName');
  }

Do not be afraid that this approach will retrieve all data and than filters it in memory. LINQ sends query to database only when you call ToList(), ToArray() or use result in foreach loop

Upvotes: 3

Related Questions