CODEWITHSUNDEEP
.netlinqlinq-to-sqldynamicwhere-clause
cosmic
cosmic

Reputation:

Dynamic Where Clause over relational tables with LINQ to SQL

I need help for a 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.

alt text

Upvotes: 6

Views: 12643

Answers (4)

Josh M.
Josh M.

Reputation: 27831

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.

Upvotes: 0

RobS
RobS

Reputation: 9422

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?

Upvotes: 12

KristoferA
KristoferA

Reputation: 12397

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}

Upvotes: 2

Aaron
Aaron

Reputation: 1483

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.

Upvotes: 5

Related Questions