Entity Framework Linq Query Plus Sql Function Nested Query

Question

I have the following linq query

public IEnumerable<DealershipWithDealersViewModel> Get(float latitude, float longitude)
        {
            return from dealer in Db.Dealerships
                          join i in Db.NearestDealers(latitude, longitude)
                          on dealer.DealerID equals i.DealerID
                          select new DealershipWithDealersViewModel
                                     {
                                         DealerID = dealer.DealerID,
                                         Dealer = dealer.Dealer,
                                         DoSales = dealer.DoSales,
                                         DoService = dealer.DoService,
                                         AddressProvinceID = dealer.AddressProvinceID,
                                         AddressLocationID = dealer.AddressLocationID,
                                         Address1 = dealer.Address1,
                                         Address2 = dealer.Address2,
                                         Tel = dealer.Tel,
                                         Fax = dealer.Fax,
                                         MapLat = dealer.MapLat,
                                         MapLong = dealer.MapLong,
                                         Location = dealer.Location.LocationName,
                                         DealerUsers = dealer.DealerUsers
                                            .Select(y => new DealerUserViewModel
                                                             {
                                                                DealerUserID = y.DealerUserID,
                                                                FirstName  = y.Firstname,
                                                                Surname = y.Surname,
                                                                LandLine = y.LandLine,
                                                                Email = y.Email,
                                                                Position = y.DealerType.DealerPosition
                                                             })

                                     };
        }

I keep getting the following error The nested query does not have the appropriate keys. I cannot find anything about it on the net. If I load the above without DealerUsers, it works as expected, but I need the nested data. Thank you! The below works by the way.

public IEnumerable<DealershipWithDealersViewModel> Get(float latitude, float longitude)
        {
            return from dealer in Db.Dealerships
                          join i in Db.NearestDealers(latitude, longitude)
                          on dealer.DealerID equals i.DealerID
                          select new DealershipWithDealersViewModel
                                     {
                                         DealerID = dealer.DealerID,
                                         Dealer = dealer.Dealer,
                                         DoSales = dealer.DoSales,
                                         DoService = dealer.DoService,
                                         AddressProvinceID = dealer.AddressProvinceID,
                                         AddressLocationID = dealer.AddressLocationID,
                                         Address1 = dealer.Address1,
                                         Address2 = dealer.Address2,
                                         Tel = dealer.Tel,
                                         Fax = dealer.Fax,
                                         MapLat = dealer.MapLat,
                                         MapLong = dealer.MapLong,
                                         Location = dealer.Location.LocationName

                                     };
        }

Update

This also works.

return Db.Dealerships.Select(x => new DealershipWithDealersViewModel
            {
                DealerID = x.DealerID,
                Dealer = x.Dealer,
                DoSales = x.DoSales,
                DoService = x.DoService,
                AddressProvinceID = x.AddressProvinceID,
                AddressLocationID = x.AddressLocationID,
                Address1 = x.Address1,
                Address2 = x.Address2,
                Tel = x.Tel,
                Fax = x.Fax,
                MapLat = x.MapLat,
                MapLong = x.MapLong,
                Location = x.Location.Location1,
                DealerUsers = x.DealerUsers.Select(y => new DealerUserViewModel
                                                            {
                                                                DealerUserID = y.DealerUserID,
                                                                FirstName = y.Firstname,
                                                                Surname = y.Surname,
                                                                LandLine = y.LandLine,
                                                                Email = y.Email,
                                                                Position = y.DealerType.DealerType1
                                                            })
            });

Answer 1

It's a matter of composablity. EF will always try to translate your query into SQL. If this succeeds, fine. If it doesn't, it's not going to try and make it work e.g. by switching to linq to objects below the hood (as linq to sql may do). You trying to join a stored procedure result to a SQL query. This can't even be done in plains SQL, because sproc results are not composable, let alone by EF.

You van only join the results in memory, by using Db.Dealerships.AsEnumerable() and Db.NearestDealers(latitude, longitude).

So it would be very useful if you could add a filter parameter DealerID to the procedure's signature.

Answer 2

What you are doing may not be possible. See: MSDN. At the bottom of the article it states

Certain types of queries that require pulling up keys from a nested query are not supported.