Linq To Sql Need Dynamic Where Clause over relational tables Help?

Question

I need Help for dynamic where clause over relational tables (one to many) in LinqToSql.

User select conditions from page. (there is 4 input that user select the clauses)

For example CompanyName and CompanyTitle from Customer table and OrderDate and ShipCity From Order table.

But user can select one ore many of them from page interface and dynamic query will be generated at codebehind and select From LinqToSql.

You can give similar type of example from another web pages.

Answer 1

Check out ScottGu's blog on the dynamic linq library. I think it will help.

Here is an example of a query that hits both the customers and orders table:

    var query =
    db.Customers.
    Where("City = @0 and Orders.Count >= @1", "London", 10).
    OrderBy("CompanyName").
    Select("new(CompanyName as Name, Phone)");
    

The query above came from the C# samples for Visual Studio. Download and look in the \LinqSamples\DynamicQuery folder and you will find more examples.

Answer 2

Here's a post that covers similar material:

http://stackoverflow.com/questions/234439/dynamic-where-clause-in-linq-with-column-names-available-at-runtime

This article might also help:

dynamic linq queries / dynamic where clause

Answer 3

Depends on how dynamic you want it to be - as others already suggested the System.Linq.Dynamic namespace adds some neat functionality for composing queries where entities/members (tables/columns) involved are not known at design time. In this case it sounds like the entities and members involved are already known and you just need to alternate between different fields as you where clause criteria. Here's an example of that:

from cust in dc.Customer
join ord in dc.Order on cust.CustomerID equals ord.CustomerID
where (companyName == null || cust.CompanyName == companyName)
  and (companyTitle == null || cust.CompanyTitle == companyTitle)
  and (orderDate == null || ord.OrderDate == orderDate)
  and (shipCity == null || ord.ShipCity == shipCity)
select new {cust, ord}

Answer 4

Thanks KristoferA for reply,

I wanna generate (companyName == null || cust.CompanyName == companyName) and (companyTitle == null || cust.CompanyTitle == companyTitle) and (orderDate == null || ord.OrderDate == orderDate) and (shipCity == null || ord.ShipCity == shipCity)

where clause by this way if companyName is entered from textbox or combobox it will add to where clause and continue to second clause with AND if OrderDate is entered it will add to clause. User can choice where clauses from page and submit the button click with this clauses

Answer 5

Are you looking for something like this, where you define the "base" query, and then evaluate parameters to determine if a where clause is appropriate?

var result = (from x in context.X
              select x);

if(some condition)
{
    result = result.AsQueryable().Where(x => x.companyName == name);
}
if(some other condition)
{
    result = result.AsQueryable().Where(x => x.companyTitle == title);
}

//return result.ToList();
//return result.FirstOrDefault();
//return result.Count(); //etc

I noticed in one of your comments you mentioned your tables are not joined by a foreign key? I'm not sure how you get a one-to-many relationship without some kind of referential integrity or relationship?

Answer 6

var result = from cust in DB.Customers
                 join custcars in DB.Customer_Cars on cust.OID equals custcars.OIDCustomer select new {cust};

I have three table Customers[OID,Name,Age] , Customer_Cars[OID,OIDCustomer,OIDCarModels] and CarModels[OID,Name]

User can select from search page one or many of inputs like customer name equal to 'tom' and age>20 and carmodels IN (BMW ; Mercedes) with checkboxes. user can create dynamicly where clause.

How can i pass these parameters to result set dynamicly?

Answer 7

RobS supplied what I think is the most attractive solution. However, this is the method I was using but then I realized that it is actually performing the first query in full (Linq-To-SQL) and then the subsequent .Where() clauses are performed with just LINQ. So this is not a viable solution as the entire set of data is enumerated and then filtered out aftwerwards in memory.

Please correct me if I'm wrong - but this is what I've noticed.