复杂的 Linq 查询未按预期工作

我想合并 4 个表的结果并使用 LINQ 选择特定字段。 请耐心等待，因为我没有做过复杂的 LINQ 查询。

表 1 - 订阅者

表 2 - 订阅

表 3 - 状态

表 4 - 国家/地区

注意：订阅者可以有 0 个、1 个或多个订阅。 这意味着外键 (SubscriberID) 是订阅表的一部分

该查询应返回订户表中的每个订户一次。订阅者是否订阅并不重要。我需要将所有订阅者都包含在结果列表中。

这就是事情变得复杂的地方：

在结果列表中，我想包含一个属性“PubName”。此属性是一个逗号分隔的字符串，其中包含订阅者订阅的 pub 名称。 PubName 是订阅表中的一列。

我用 SQL 编写了一个存储过程，使用附加函数来构造每个订阅者的 PubName 字段。

例如：我们的列表有 3 行：

1. 维克多，123 W 45th st #43，纽约，纽约，“Mag A、Mag B、Mag C”

（Victor 订阅了 Mag A、B 和 C）

2. Dan，564 E 23rd st FL3，纽约，纽约，“Mag A、Mag D、Mag F”

（Dan 订阅了 Mag A、D 和 F）

3. 妮可，78 E 12rd st #3，纽约，纽约，“NULL”

（妮可没有订阅）

    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";
        }                
    }

使用以下代码我收到此错误：

“LINQ to Entities 无法识别“System.String GetPubName(System.Nullable`1[System.Int32])”方法，并且此方法无法转换为存储表达式。”

我理解这个错误。方法无法转换为 LINQ 语句内的存储表达式。

1. 在 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();

结果不包括未订阅的订阅者。 有什么想法如何修复它吗？

结果不包括未订阅的订阅者。

编写查询时，始终首先尝试确定根实体。您对订阅感兴趣，因此显然应该采取Subscription作为根实体。但实际上你想看看是否订户有订阅，如果有，是哪些。订阅者是根实体，因此从那里开始查询。

弄清楚如何连接字符串

Sure, db.Subscriptions.ToList()确实允许您执行 LINQ-to-objects 所存储的任何操作，但效率非常低。首先，你拉动所有Subscription数据存入内存。然后，在var model = (from s in query ...你加入DbSet每个人都拉动所有their数据存入内存。 （因为query is IEnumerable因此，不能与IQueryables 转换为一个表达式，然后转换为一个 SQL 语句）。

在 LINQ-to-Entities 查询中使用不支持的方法的策略是：查询确切的数据量——不多也不少——然后在内存中继续。

这两点都相当于这个查询：

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 结果集被大大低估了缩短通过过滤它。