Complex Linq query is not working as expected

Question

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.

Table 1 - Subscriber

Table 2 - Subscription

Table 3 - State

Table 4 - Country

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.

Here is where it gets complicated:

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.

I have written a Stored Procedure in SQL using an additional function to construct the PubName field per subscriber.

For Example: our list has 3 rows:

1. Victor, 123 W 45th st #43, New York, NY, 'Mag A, Mag B, Mag C'

(Victor is subscribed to Mag A, B,and C)

2. Dan, 564 E 23rd st FL3, New York, NY, 'Mag A, Mag D, Mag F'

(Dan is subscribed to Mag A, D,and F)

3. Nicole, 78 E 12rd st #3, New York, NY, 'NULL'

(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 does not recognize the method 'System.String GetPubName(System.Nullable`1[System.Int32])' method, and this method cannot be translated into a store expression."

I understand the error. A method cannot be translated into store expression inside a LINQ statment.

1. Is it possible to achieve this in LINQ? If so, can someone show me how? I am unable to find a solution.

FIGURED OUT HOW TO CONCATENATE THE STRING:

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?

Answer 1

The results is not including subscribers without subscriptions.

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

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).

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.

Both points amount to this query:

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

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.

Answer 2

It's not really LINQ that's tripping up here, it's LINQ to Entities. Are you using Entity Framework? Does your model have the relationships defined in it? If you have foreign keys in your database, and build your model in Entity Framework with database first, it will map all the entity relationships for you.

If yes, you can then do something like:

using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public class Subscriber{
        public string Name {get;set;}
        public List<Subscription> Subscriptions{get;set;}
    }

    public class Subscription{
        public string Name {get;set;}
    }

    public class MyViewModelItem{
        public string SubscriberName{get;set;}
        public string SubscriptionNames {get;set;}
    }

    public static void Main()
    {
        Console.WriteLine("Hello World");

        // create some dummy data
        var data = new List<Subscriber>{
            new Subscriber{
                Name = "Arnold",
                Subscriptions = new List<Subscription>(){
                    new Subscription{
                        Name = "Subscription A"
                    },
                    new Subscription{
                        Name = "Subscription B"
                    },
                    new Subscription{
                        Name = "Subscription C"
                    }
                }
            },
            new Subscriber{
                Name = "Betty",
                    Subscriptions = new List<Subscription>()
            },
            new Subscriber{
                Name = "Christopher",
                Subscriptions = new List<Subscription>(){
                    new Subscription{
                        Name = "Subscription A"
                    }
                }
            }
        };

        // here's the query and it becomes much simpler
        var myViewModel = data
            .Select(i=> new MyViewModelItem{
                SubscriberName = i.Name,
                SubscriptionNames = string.Join(", ", i.Subscriptions.Select(j=>j.Name))
            })
            .ToList();

        // this shows the output
        foreach(var item in myViewModel){
            Console.WriteLine(string.Format("subscriber: {0}, subscriptions: {1}",item.SubscriberName,item.SubscriptionNames));
        }

    }
}

Output:

Hello World subscriber: Arnold, subscriptions: Subscription A, Subscription B, Subscription C subscriber: Betty, subscriptions: subscriber: Christopher, subscriptions: Subscription A