ASP.NET MVC class inheritence and LINQ

Question

I'm having a problem with LINQ to entities which I've resolved, but want to make sure I've resolved in the right way.

I have 2 classes:

namespace ShopTest.Models
{

public class Shop
{
    public int ShopID { get; set; }
    public string Name { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Postcode { get; set; }
    public string Country { get; set; }
    public decimal Latitude { get; set; }
    public decimal Longitude{ get; set; }
}

[NotMapped]
public class ShopLocation : Shop
{
    public decimal AddressLatitude { get; set; }
    public decimal AddressLongitude { get; set; }
    public decimal DistanceFromAddress
    {
        get
        {
            return Convert.ToDecimal(
                        Math.Sqrt(
                                Math.Pow(Convert.ToDouble(this.Latitude - this.AddressLatitude), 2.0)
                                    +
                                Math.Pow(Convert.ToDouble(this.Longitude- this.AddressLongitude), 2.0)
                        )
                        * 62.1371192
                    );
        }
    }
}

}

When querying in LINQ, I originally tried:

decimal lat = Convert.ToDecimal(-33.8736510, NumberFormatInfo.InvariantInfo);
decimal lng = Convert.ToDecimal(151.2068896, NumberFormatInfo.InvariantInfo);

var nearbyShops = from c in db.Shops
                   where Math.Abs(c.lat - lat) < 0.25M &&
                         Math.Abs(c.lng - lng) < 0.25M
                   select new NearbyShopLocation()
                   {
                       StoreNumber = store.StoreNumber,
                       Address = store.Address,
                       City = store.City,
                       Region = store.Region,
                       CountryCode = store.CountryCode,
                       PostalCode = store.PostalCode,
                       Latitude = store.Latitude,
                       Longitude = store.Longitude,
                       AddressLatitude = lat,
                       AddressLongitude = lng
                   };

var nearbySortedShops = nearbyShops.ToList().OrderBy(s => s.DistanceFromAddress).ToList();

However I kept getting the error "The entity or complex type 'ShopTest.Controllers.Shops' cannot be constructed in a LINQ to Entities query"

I've fixed the issue with the below code, but it doesn't make sense why this would work - being new to MVC I'm hoping someone can explain it. :-)

var nearbyShops = (from c in db.Shops
                  where Math.Abs(c.lat - lat) < 0.25M &&
                        Math.Abs(c.lng - lng) < 0.25M
                  select new 
                  {
                      StoreNumber = c.StoreNumber,
                      Address = c.Address,
                      City = c.City,
                      Country = c.Country,
                      PostalCode = c.PostalCode,
                      Latitude = c.Latitude,
                      Longitude = c.Longitude,
                  }).ToList().Select(l => new ShopLocation
                  {
                       Name = l.Name,
                       City = l.City,
                       State = l.State,
                       Country = l.Country,
                       Lat = l.Lat,
                       Lng = l.Lng,
                       AddressLatitude = lat,
                       AddressLongitude = lng
                  }).ToList().OrderBy(s => s.DistanceFromAddress).ToList();

Have I done this correctly? Is there a better way?

Ladislav Mrnka · Accepted Answer · 2012-04-25 07:35:24Z

1

EF has limitation that you cannot build mapped entity manually in the query. That means you cannot do this:

 var shops = from s in db.Shops where ... select new Shop { ... };

This also involves derived entities. Because of that you first have to call ToList to switch to Linq-to-Objects:

 var shopse = db.Shops.Where(...).ToList().Select(s => new Shop { ... });

Generally you should be OK with:

var nearbyShops = 
             (from c in db.Shops
              where Math.Abs(c.lat - lat) < 0.25M &&
                    Math.Abs(c.lng - lng) < 0.25M
              select c).ToList()
             .Select(l => new ShopLocation
              {
                   Name = l.Name,
                   City = l.City,
                   State = l.State,
                   Country = l.Country,
                   Lat = l.Lat,
                   Lng = l.Lng,
                   AddressLatitude = lat,
                   AddressLongitude = lng
              }).OrderBy(s => s.DistanceFromAddress).ToList();

answered Apr 25, 2012 at 7:35

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

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

1 Comment

Robbie Mills Over a year ago

Great response, very helpful and educational, and worked perfectly. Thank you!

Collectives™ on Stack Overflow

ASP.NET MVC class inheritence and LINQ

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related