Linq To Sql需要动态Where子句关系表帮助？

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

我需要在LinqToSql中关于关系表(一对多)的动态where子句的帮助。

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

用户从页面选择条件。 (用户选择条款有4个输入)

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

例如,来自Customer表的CompanyName和CompanyTitle以及OrderDate和ShipCity From Order表。

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

但是用户可以从页面界面中选择其中一个,并在代码隐藏时生成动态查询,并选择From LinqToSql。

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

您可以从其他网页提供类似类型的示例。

alt text http://www.yazgelistir.com/Makaleler/Resimler/1000001875_Linq-Class-Server.jpg

替代文字http://www.yazgelistir.com/Makaleler/Resimler/1000001875_Linq-Class-Server.jpg

4 个解决方案

#1

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?

您是否正在寻找这样的东西,您在其中定义“基础”查询,然后评估参数以确定where子句是否合适?

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?

我在你的一条评论中注意到你提到你的表没有用外键加入?如果没有某种参照完整性或关系,我不确定你如何得到一对多的关系?

#2

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

查看ScottGu关于动态linq库的博客。我认为这会有所帮助。

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.

上面的查询来自Visual Studio的C#示例。下载并查看\ LinqSamples \ DynamicQuery文件夹,您将找到更多示例。

#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:

取决于您希望它的动态程度 - 正如其他人已经建议的那样,System.Linq.Dynamic命名空间为编写查询添加了一些简洁的功能,其中涉及的实体/成员(表/列)在设计时是未知的。在这种情况下,它听起来像所涉及的实体和成员已知,并且您只需要在where子句标准之间在不同字段之间切换。这是一个例子:

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}

#4

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.

RobS提供了我认为最有吸引力的解决方案。但是,这是我使用的方法,但后来我意识到它实际上是完全执行第一个查询(Linq-To-SQL),然后只使用LINQ执行后续.Where()子句。因此,这不是一个可行的解决方案,因为整个数据集被枚举,然后在内存中向后过滤掉。

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

如果我错了请纠正我 - 但这是我注意到的。

#1