复杂的Linq查询无法正常工作 [英] Complex Linq query is not working as expected
问题描述
我想合并来自4个表的结果，并使用LINQ选择特定的字段. 因为我还没有完成复杂的LINQ查询，所以请多多包涵.
I want to combine results from 4 tables and select specific fields using LINQ. Please bear with me since I have not done complex LINQ queries.
表1-订户
表2-订阅
表3-状态
表4-国家/地区
注意:订户可以有0个，1个或多个订阅. 这意味着外键(SubscriberID)是Subscription表的一部分
NOTE: A subscriber can have 0, 1, or many subscriptions. This means a foreign key (SubscriberID) is part of the Subscription table
查询应该从订户表一次返回每个订户.订户是否有订阅无关紧要.我需要在结果列表中包含所有订户.
The query should return every subscriber from the subscriber table once. Whether the subscriber has a subscription or not it does not matter. I need to have all of my subscribers in the result list.
这是复杂的地方:
在结果列表中，我想包含一个属性"PubName".此属性是一个逗号分隔的字符串，其中包含订阅者所订阅的发布者名称. PubName是订阅"表中的一列.
In the result list I want to include a property 'PubName'. This property is a comma separated string with the pub names that the subscriber is subscribed to. The PubName is a column in the Subscription table.
我已经用SQL编写了一个存储过程，并使用了一个附加功能来构造每个订阅者的PubName字段.
I have written a Stored Procedure in SQL using an additional function to construct the PubName field per subscriber.
例如:我们的列表有3行:
For Example: our list has 3 rows:
- Victor，纽约州纽约市W 45th st#43#123号，"Mag A，Mag B，Mag C"
(Victor订阅了Mag A，B和C)
(Victor is subscribed to Mag A, B,and C)
- 丹，564 E 23rd st FL3，纽约，纽约，"Mag A，Mag D，Mag F"
(Dan已订阅Mag A，D和F)
(Dan is subscribed to Mag A, D,and F)
- Nicole，78 E 12rd st#3，纽约，纽约，"NULL"
(Nicole没有订阅)
(Nicole has no subscriptions)
var model = await (
from subscriber in db.Subscribers
// left join
from state in db.States.Where(s => s.State_ID == subscriber.SubscriberState_ID).DefaultIfEmpty()
// left join
from country in db.Countries.Where(s => s.Country_ID == subscriber.SubscriberCountry_ID).DefaultIfEmpty()
orderby subscriber.Subscriber_ID descending
select new SubscriberGridViewModel
{
Subscriber_ID = subscriber.Subscriber_ID,
Pub = GetPubName(subscriber.Subscriber_ID).ToString(),
FirstName = subscriber.SubscriberFirstName,
LastName = subscriber.SubscriberLastName,
Address1 = subscriber.SubscriberAddress1,
Address2 = subscriber.SubscriberAddress2,
Email = subscriber.SubscriberEmail,
Organization = subscriber.SubscriberOrganizationName,
Phone = subscriber.SubscriberPhone,
Zip = subscriber.SubscriberZipcode
}).ToListAsync();
private static string GetPubName(int? subscriber_id)
{
string pubs = string.Empty;
try
{
var db = new CirculationEntities();
var model = db.Subscriptions.Where(s => s.Subscriber_ID == subscriber_id).ToList();
foreach(Subscription sub in model)
{
if (string.IsNullOrEmpty(pubs))
pubs = sub.SubscriptionPublication;
else
pubs = ", " + sub.SubscriptionPublication;
}
return pubs;
}
catch
{
return "EMPTY";
}
}
使用以下代码，我收到此错误:
with the following code I am getting this error:
"LINQ to Entities无法识别方法'System.String GetPubName(System.Nullable`1 [System.Int32])'方法，并且该方法无法转换为商店表达式."
"LINQ to Entities does not recognize the method 'System.String GetPubName(System.Nullable`1[System.Int32])' method, and this method cannot be translated into a store expression."
我了解错误.无法将方法转换为LINQ语句内的商店表达式.
I understand the error. A method cannot be translated into store expression inside a LINQ statment.
- 是否可以在LINQ中实现这一目标?如果是这样，有人可以告诉我如何?我找不到解决方法.
弄清楚如何连接字符串:
var query = from subscription in db.Subscriptions.ToList()
group subscription by subscription.Subscriber_ID into g
select new
{
Subscriber_ID = g.Key,
Pub = string.Join(", ", g.Select(x => x.SubscriptionPublication).Distinct())
};
var model = (from s in query
join subscriber in db.Subscribers on s.Subscriber_ID equals subscriber.Subscriber_ID
join state in db.States on subscriber.SubscriberState_ID equals state.State_ID
join country in db.Countries on subscriber.SubscriberCountry_ID equals country.Country_ID
select new SubscriberGridViewModel
{
Subscriber_ID = subscriber.Subscriber_ID,
Pub = s.Pub,
FirstName = subscriber.SubscriberFirstName,
LastName = subscriber.SubscriberLastName,
Address1 = subscriber.SubscriberAddress1,
Address2 = subscriber.SubscriberAddress2,
Email = subscriber.SubscriberEmail,
Organization = subscriber.SubscriberOrganizationName,
Phone = subscriber.SubscriberPhone,
City = subscriber.SubscriberCity,
State = (subscriber.SubscriberState_ID == 54) ? subscriber.SubscriberState : state.StateName,
StateAbbv = (subscriber.SubscriberState_ID == 54) ? subscriber.SubscriberState : state.StateAbbreviation,
Country = country.CountryName,
Zip = subscriber.SubscriberZipcode
}).ToList();
结果不包括没有订阅的订阅者. 有任何解决方法的想法吗?
The results is not including subscribers without subscriptions. Any ideas how to fix it?
推荐答案
结果不包括没有订阅的订阅者.
The results is not including subscribers without subscriptions.
编写查询时，请始终先尝试确定根实体.您对订阅感兴趣，因此将Subscription作为根实体似乎很明显.但实际上，您想查看订户是否有订阅，如果有，则有.订户是根实体，因此从那里开始查询.
When writing queries, always first try to determine the root entity. You're interested in subscriptions, so it seems obvious to take Subscription as root entity. But in fact you want to see whether or not subscribers have subscriptions, and if so, which. Subscriber is the root entity, so start the query there.
弄清楚如何连接字符串
Figured out how to concatenate the string
当然，db.Subscriptions.ToList()确实允许您执行LINQ到对象存储中的任何操作，但是效率很低.首先，将所有Subscription数据拉入内存.然后，在var model = (from s in query ...中，您将与DbSet一起加入，每个DbSet会将所有其数据拉入内存. (因为query是IEnumerable，因此不能与IQueryable组合为一个表达式，然后转换为一个SQL语句.)
Sure, db.Subscriptions.ToList() does allow you to do anything that LINQ-to-objects has in store, but it's very inefficient. First, you pull all Subscription data into memory. Then, in var model = (from s in query ... you join with DbSets that each pull all their data into memory. (Because query is IEnumerable and, hence, can't be combined with IQueryables into one expression and then translated into one SQL statement).
在LINQ-to-Entities查询中使用不受支持的方法的策略是:查询确切的数据量-不多，不少-然后在内存中继续.
The strategy for using non-supported methods in LINQ-to-Entities queries is: query the exact amount of data --no more, no less-- then continue in memory.
两个点都等于该查询:
var query = from s in db.Subcribers // root entity
select new
{
Subscriber_ID = s.Subscriber_ID,
FirstName = s.SubscriberFirstName,
LastName = s.SubscriberLastName,
Address1 = s.SubscriberAddress1,
Address2 = s.SubscriberAddress2,
Email = s.SubscriberEmail,
Organization = s.SubscriberOrganizationName,
Phone = s.SubscriberPhone,
City = s.SubscriberCity,
Zip = s.SubscriberZipcode,
// Navigation properties here
State = (s.SubscriberState_ID == 54) ? s.SubscriberState : s.State.StateName,
StateAbbv = (s.SubscriberState_ID == 54) ? s.SubscriberState : s.State.StateAbbreviation,
Country = s.Country.CountryName,
// Empty list when no subscriptions
Pubs = s.Subscriptions.Select(x => x.SubscriptionPublication).Distinct()
};
var result = query.AsEnumerable() // continue in memory
Select(s => new SubscriberGridViewModel
{
Subscriber_ID = s.Subscriber_ID,
FirstName = s.FirstName,
LastName = s.LastName,
Address1 = s.Address1,
Address2 = s.Address2,
Email = s.Email,
Organization = s.Organization,
Phone = s.Phone,
City = s.City,
State = s.State,
StateAbbv = s.StateAbbv,
Country = s.Country,
Zip = s.Zip
Pub = string.Join(", ", s.Pubs)
}));
当然，如果您要查询Subscriber中的几乎所有字段，这可能会有些冗长:select new { Subscriber = s, Pubs = .. }等.但是我通常会体验到缩小的性能.与通过过滤将其缩短 相比，SQL结果集被大大低估了.
Of course, if you're querying almost all fields from Subscriber this can be a bit less verbose: select new { Subscriber = s, Pubs = .. } etc. But I usually experience that the performance gain of narrowing down the SQL result set is greatly underestimated as compared to shortening it by filtering.
这篇关于复杂的Linq查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
