涉及.include的简单Linq-to-entities查询，我相信 [英] Simple Linq-to-entities query involving .Include I believe

问题描述

我有一个Linq-to-Entities查询，它并不复杂，但是需要一个.include和/或投影和/或联接，因为它必须一次性执行.

I have a Linq-to-Entities query that is not complicated but requires an .include and/or projection and/or join because it must be executed in one pass.

这是我的数据库(Microsoft SQL Server 2008):

Here is my database (Microsoft SQL Server 2008):

表A(客户)(包含CustomerID(客户ID)和ZipCode(邮政编码)作为字符串.

Table A (Customers) (contains CustomerID (customer IDs), and ZipCode (zip codes) as strings.

表C(类别)(包含类别ID"(类别)，例如食物"，庇护所"，衣服"，房屋"(主键).

Table C (Categories) (contains CategoryID (categories) like "food", "shelter","clothing", "housing" (primary keys).

表A_C是一个链接表，因为表A和C被多对多链接:仅包含两个字段:CustomerID客户ID"和CategoryID(类别)，作为主键组合在一起.该表是表A和C之间的链接表.

Table A_C is a linking table, since Tables A and C are linked as many-to-many: contains just two fields: CustomerID "customer IDs" and CategoryID (Categories), in combination as primary keys. This table is a linking table betweeen tables A and C.

这是我的查询，必须在一次数据库访问中执行:我需要选择表A中满足条件的所有记录，然后根据在中找到的参数列表"过滤这些记录链接表A_C-并一站式完成对数据库的所有操作.但是我不知道表A_C的参数列表的长度或组成是什么提前，它随调用而变化.因此，此参数列表因方法调用而异.

Here is my query, that must be executed in just one trip to the database: I need to select all records in Table A that satisfy a condition, then filter these records depending on a 'list of parameters' that are found in the linking Table A_C--and do this all in one trip to the database. But I don't know what the length or composition of the list of parameters for Table A_C is, ahead of time--it varies from call to call. Thus this list of parameters varies method call by method call.

举一个更具体的例子:

表A列出了客户ID.我找到了居住在特定邮政编码中的客户.然后，在同一SQL查询中，我需要找到以下哪些客户选择了某些类别:食物，服装，房屋等，但是我的网络方法无法提前知道这些类别是什么，而是将它们作为列表传递给方法:List myCategoryList(可以是1个类别或100个类别，并根据方法调用而变化方法调用.)

Table A has a list of customer IDs. I find the customers that live in a certain Zip code. Then, in the same SQL query, I need to find which of these customers have selected certain categories: Food, Clothing, Housing, etc, but my web method does not know ahead of time what these categories are, rather, they are passed as a list to the method: List myCategoryList (which could be 1 category or 100 categories, and varies method call by method call).

如何使用Linq-to-Entities编写投影?当参数列表有所不同时?一口气做完所有事情吗?

How do I write the projection using Linq-to-Entities? When the list of parameters varies? And do it all in one pass?

  List<string> CategoryList = new List<string>() { "Food", "Shelter", "Housing" }; // in one call to the web service method

   List<string> CategoryList = new List<string>() { "Food", "Clothing" }; //could be a second call--it varies and I don't know ahead of time what the List will be

那么我该如何使用Linq-to-Entities进行SQL查询?一口气? (当然，我可以遍历该列表，并重复访问数据库，但这并不是我所知的最佳解决方案). Projection，.Include是关键字，但是上网冲浪却无济于事.

So how can I do the SQL query using Linq-to-Entities? In one pass? (Of course I could loop through the list, and make repeated trips to the database, but that's not an optimal solution I am told). Projection,.Include are keywords but surfing the net yielded nothing.

这是一个粗略的猜测，只是为了使球滚动:

Here is a crude guess, just to get ball rolling:

 public void WebMethod1 (CategoryList)
 {

 using (EntityFramework1 context = new EntityFramework1())

 {
  /* assume CategoryList is a list of strings passed into the method and is,for     this      particular call,something like:  List<string> CategoryList = new List<string>() { "Food", "Clothing" }; for this call,      but in the next call it could be: List<string> CategoryList = new List<string>() { "Food", "Shelter", "Housing" } */

 string ZipCodeString = "12345";
 string customerIDString = "E12RJ55";

 var CustomersFromZipCodeHavingSelectedCertainCategories =  from x in context.A_C
                            where x.A.CustomerID == customerIDString
                            where x.A.StartsWith(ZipCodeString)
                            where x.A_C.Contains(CategoryList) //???? This is clearly not grammatical, but what is?
                            select x;

 }

/*

我的问题是:我想过滤来自A的所有记录，其中包含邮政编码12345，并且这些记录也具有来自表A的特定CustomerID"E12RJ55"，但是在链接表A_C中进一步使用所有此类CustomerID过滤此集食品"和服装"类别.

my problem is: I want to filter all records from A that contain a zipcode 12345, and that also have a certain CustomerID "E12RJ55" from table A, but further filter this set with all such CustomerIDs in linking table A_C that contain the categories "Food" and "Clothing".

如何一口气做到这一点?我可以使用代码在多次传递和访问数据库的过程中很容易地做到这一点，但是该线程中的某个人在这里 http://bit. ly/rEG2AM 建议我做一个Join/投影，然后一次完成.

How to do this in one pass? I can do this quite easily in multiple passes and trips to the database using code, but somebody in this thread here http://bit.ly/rEG2AM suggested I do a Join/projection and do it all in one fell swoop.

*/

我也将接受SQL答案，因为它可能有助于解决问题.我相信这个问题并不难，但我无法在网上找到答案.

I will also accept SQL answers since it might help yield a solution. This question btw is not difficult I believe--but I could not find an answer on the net.

对大卫的回答和功劳. 我感谢您的回答david.s.这是有效的方法，与david.s的答案稍有不同，在于我使用的是称为"Customer_Categories"的链接表(桥接表)，该表位于Customer表和Categories之间，并包含每个表的主键(按要求)用于多对多关系).这个桥表就是我在原始答案中所说的"A_C"，这里有整数而不是字符串，但是是同一回事. Intellisense拿起这张桌子，我用了它，它可以工作.还请记住，CategoryList是一个整数列表，List CategoryList = new List();，但令人惊讶的是，它可以自动在此SQL-to-Entities查询中工作:

with answer and credit to david s. I thank you for the answer david.s. Here is what worked, slightly different than the answer by david.s, in that I am using the linking table (bridge table) called "Customer_Categories" that is between the table Customer and Categories and contains the primary key of each (as is required for many-to-many relationships). This bridge table is what I called "A_C" in my original answer, and here has ints rather than strings but is the same thing. Intellisense picked up this table and I used it, and it works. Also keep in mind that CategoryList is a list of ints, List CategoryList = new List();, yet amazingly it automagically works inside this SQL-to-Entities query:

Var CustomersFromZipCOde = context.Customers.Where (custo => custo.CustomerID==customerIDString && custo.ZipCode.StartsWith(ZipCodeString) && custo.Customer_Categories.Any(categ => CategoryList.Contains(categ.CategoryID)));

//gives the right output, incredible.

推荐答案

首先，我想说的是，即使您的解释很长，也不是很清楚.您想要一个简单的Linq-to-Entities查询，但您不提供Entities，只谈论数据库中的表.

First of all i would like to say that even if you explanation is very long it is not very clear. You would like a simple Linq-to-Entities query but you don't give the Entities, you only speak of tables in your database.

假设您具有以下实体:

public class Customer
{
    public string CustomerID { get; set; }
    public string ZipCode { get; set; }
    public virtual ICollection<Category> Categories { get; set; }
}

public class Category
{
    public string CategoryID { get; set; }
    public virtual ICollection<Customer> Customers { get; set; }
}

您的查询可能如下所示:

Your query might look like this:

var CustomersFromZipCodeHavingSelectedCertainCategories =
    context.Customers.Where(
        customer => customer.CustomerID == customerIDString &&
                    customer.ZipCode.StartsWith(ZipCodeString) &&
                    customer.Categories.Any(
                        category => CategoryList.Contains(category.CategoryID));

此处提供有关其他方法的更多信息: http://smehrozalam.wordpress.com/2010/06/29/entity-framework-queries-involving许多对许多关系表/

More info on other ways to do this here: http://smehrozalam.wordpress.com/2010/06/29/entity-framework-queries-involving-many-to-many-relationship-tables/

这篇关于涉及.include的简单Linq-to-entities查询，我相信的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！