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

                                     };
        }

enter image description here 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

                                     };
        }

enter image description here

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

enter image description here

can you try DealerID = dealer.DealerId instead of DealerID = i.DealerId? — Pawel
– Pawel, Commented Feb 17, 2013 at 19:05
I did and makes no difference. Kinda stumped on this one. Will update the answer so people are not confused. — TYRONEMICHAEL
– TYRONEMICHAEL, Commented Feb 17, 2013 at 19:14
It is a sql function that calculates the nearest dealers based on their Latitude and Longitude values. — TYRONEMICHAEL
– TYRONEMICHAEL, Commented Feb 17, 2013 at 19:16

Ryan Byrne · Accepted Answer · 2013-02-17 19:03:51Z

1

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.

answered Feb 17, 2013 at 19:03

Ryan Byrne

8605 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gert Arnold · Accepted Answer · 2013-02-18 00:34:54Z

0

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.

answered Feb 18, 2013 at 0:34

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

1 Comment

TYRONEMICHAEL Over a year ago

Thanks for your help. Just ended up writing a stored procedure and bypassed the linq to sql completely.

Collectives™ on Stack Overflow

Entity Framework Linq Query Plus Sql Function Nested Query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related