Only parameterless constructors and initializers are supported in LINQ to Entities

Question

While trying a sem-complex query to display some ListView content on the page I got stuck on the famous "Only parameterless contstructor and initializers are supported in LINQ to Entities" error.

Here is the code I used ... I can't find a place where I initialized something inside the query with parameters ....

protected void ArtistsList()
{
    Guid cat1 = new Guid("916ec8ae-8336-43b1-87c0-8536b2676560");
    Guid cat2 = new Guid("92f2a07f-0570-4521-870a-bf898d1e92d6");

    var memberOrders = (from o in DataContext.OrderSet
                        where o.Status == 1 || o.Status == 0
                        select o.ID);

    var memberOrderDetails = (from o in DataContext.OrderDetailSet
                              where memberOrders.Any(f => f == o.Order.ID)
                              select o.Product.ID );

    var inventoryItems = (from i in DataContext.InventoryItemSet
                          select i.Inventory.Product.ID);

    var products = (from p in DataContext.ProductSet
                    join m in DataContext.ContactSet on p.ManufacturerID equals m.ID
                    where p.Active == true
                       && p.ShowOnWebSite == true
                       && p.Category.ID != cat1
                       && p.Category.ID != cat2
                       && p.AvailableDate <= DateTime.Today
                       && (p.DiscontinuationDate == null || p.DiscontinuationDate >= DateTime.Today)
                       && memberOrderDetails.Any(f => f != p.ID)
                       && inventoryItems.Any(f => f == p.ID)
                    select new { ContactID = m.ID, ContactName = m.Name });

    artistsRepeater.DataSource = products;
    artistsRepeater.DataBind();

    Response.Write("PRODUCT COUNT: " + products.Count());
}

The error itself pops on the line artistsRepeater.DataSource = products;

I tried to comment the lines && memberOrderDetails.Any(f => f != p.ID) and && inventoryItems.Any(f => f == p.ID) , still doesn't change anything

Any hints ?

[edit]

With LINQpad, it works with the join but with it is bugging on the commented line

(from p in Products
join m in Members on p.ManufacturerID.Value equals m.ID
where p.Active == true
&& p.ShowOnWebSite == true
&& p.AvailableDate <= DateTime.Today
&& (p.DiscontinuationDate == null || p.DiscontinuationDate >= DateTime.Today)
//&& (from od in MemberOrderDetails where (from mo in MemberOrders where mo.Status == 1 || mo.Status == 0 select mo.ID).Any(f => f == od.ID) select od.Product.ID)
&& (from inv in InventoryItems select inv.Inventory.ProductID).Any(i => i.Value == p.ID)
select m).Distinct()

[edit-2]

It seems that this query in LINQpad is ok :

(from p in Products
join m in Members on p.ManufacturerID.Value equals m.ID
where p.Active == true
&& p.ShowOnWebSite == true
&& p.AvailableDate <= DateTime.Today
&& (p.DiscontinuationDate == null || p.DiscontinuationDate >= DateTime.Today)
&& !(from od in MemberOrderDetails where (from mo in MemberOrders where mo.Status == 1 || mo.Status == 0 select mo).Any(f => f.ID == od.ID) select od.Product.ID).Any(i => i == p.ID)
&& (from inv in InventoryItems select inv.Inventory.ProductID).Any(i => i.Value == p.ID) 
select m)

Answer 1

The most likely culprit is:

select new { ContactID = m.ID, ContactName = m.Name }

This is because anonymous types do not have parameterless constructors. What's odd about that is that anonymous types are de riguer in LINQ to Entities. I just don't see any other line that could be offending.

First try removing that line and see if the error goes away. At least we'll know if it's that line or not. Then we can focus on figuring out why.

Edit: What are the types of OrderSet.ID, Product.ID and Order.ID and ContactSet.ID? Are any of them Guid and implicitly the Guid constructor is being called?

Answer 2

OK, this is subtle, but what if you change your LINQPad query from:

           (from p in Products
            join m in Members 
                on p.ManufacturerID.Value equals m.ID
            where p.Active == true
                && p.ShowOnWebSite == true
                && p.AvailableDate <= DateTime.Today
                && (p.DiscontinuationDate == null || p.DiscontinuationDate >= DateTime.Today)
                && (from od in MemberOrderDetails 
                    where (from mo in MemberOrders 
                           where mo.Status == 1 || mo.Status == 0 
                           select mo.ID).Any(f => f == od.ID) 
                    select od.Product.ID)
                && (from inv in InventoryItems 
                    select inv.Inventory.ProductID).Any(i => i.Value == p.ID)

...to:

           (from p in Products
            join m in Members 
                on p.ManufacturerID.Value equals m.ID
            where p.Active == true
                && p.ShowOnWebSite == true
                && p.AvailableDate <= DateTime.Today
                && (p.DiscontinuationDate == null || p.DiscontinuationDate >= DateTime.Today)
                && (from od in MemberOrderDetails 
                    where (from mo in MemberOrders 
                           where mo.Status == 1 || mo.Status == 0 
                           select mo).Any(f => f.ID == od.ID)          // NOTE!
                    select od.Product.ID)
                && (from inv in InventoryItems 
                    select inv.Inventory.ProductID).Any(i => i.Value == p.ID)

Why? I think type inference might be doing you wrong here. I've seen a similar thing with DateTimes.

Answer 3

Convert to a list first, then call your select statement:

var res = abc.getEmployess().toList().select(x => new keyvaluepair<int, string>(x.EmpID, x.EmpName.tostring)).tolist();