Casting errors when attempting to return an IQueryable<MyType>

Question

I have a query that ought to return an IQueryable<MyType>. The code looks like this:

public IQueryable<MyType> GetFooList()
{
    var query = (from x in dbContext.TableX
                 join y in dbContext.TableY on x.our_id equals y.our_id 
                 join z in dbContext.TableZ on y.our_id equals z.our_id 
                 join a in dbContext.TableA on z.other_id equals a.other_id 
                 where !string.IsNullOrEmpty(x.status)
                 select new
                 {
                   (fields....)
                 })
                 .AsQueryable();
    IQueryable<MyType> result = (IQueryable<MyType>) query;
    return result;
}

In the calling controller actions, I want to filter this list for values specified at run time; the parameters to filter for will differ between the various calling actions. E.g.:

List<MyType> FooList = Interface.GetFooList()
    .Where( specific conditions )
    .ToList();

On the line setting result, an exception gets raised:

Invalid Cast Exception was unhandled by user code

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery'1[<>f__AnonymousType9'9[System.String,System.Nullable`1[System.DateTime],System.String,System.String,System.String,System.String,System.Int32,System.Nullable'1[System.DateTime],System.Nullable'1[System.DateTime]]]' to type 'System.Linq.IQueryable'1[MyType]'.

So I figured this was a casting problem, and added .Cast<MyType>() before the call to AsQueryable(). This generates a different error:

Unable to cast the type 'Anonymous type' to type 'MyType'. LINQ to Entities only supports casting EDM primitive or enumeration types.

And if I don't do any casting, these errors get raised in the calling actions instead of the Entity Frameworks accessor.

I've tried the suggestions in all the linked "Similar Questions", to no avail -- the errors keep going back and forth. I even tried including .Select(obj => new MyType() {fields...} ) to get away from the anonymous type. That didn't work either.

I feel like I'm missing something subtly obvious.

Edited to add

I updated the code to select a type: select new MyType() {fields...}. This worked properly. Then the calling method threw a NotSupportedException, on the line where I filter the results and make a list from the query:

The entity or complex type 'MyType' cannot be constructed in a LINQ to Entities query.

ETA2

I copied the EF table properties to a new class, MyTypeDTO. I replaced all use of MyType with MyTypeDTO. I got this error:

The specified type member 'our_id' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Here's the property in the DTO:

public int our_id { get; set; }

So I removed the get/set, rebuilt, and reran. Nope, I got the same error.

Answer 1

You are correct, the reason why all of your casts have failed is that an anonymous type (i.e. the thing you create with select new {...} construct) cannot be cast to a named type.

I even tried including .Select(obj => new MyType() {fields...} ) to get away from the anonymous type. That didn't work either.

You were on the right track - this should work, assuming that MyType has the appropriate setters:

 var query = from x in dbContext.TableX
             join y in dbContext.TableY on x.our_id equals y.our_id 
             join z in dbContext.TableZ on y.our_id equals z.our_id 
             join a in dbContext.TableA on z.other_id equals a.other_id 
             where !string.IsNullOrEmpty(x.status)
             select new MyType
             {
                MyTypeField1 = a.Column1
             ,  MyTypeField2 = z.Column3
             ,  // ...and so on
             }; // No need to convert to IQueryable - this should produce the right type
return result;

The entity or complex type 'MyType' cannot be constructed in a LINQ to Entities query.

This is because MyType is a mapped entity. You should not be creating projections that return mapped entities, so EF correctly restricts your ability to do so. You should be able to project into a non-mapped type (to create a so-called DTO - data transfer object).

Define a class MyTypeDto which has the properties of MyClass, but no methods or mappings. Use MyClassDto in the definition of your method. This should fix the problem.

Answer 2

The problem was incredibly subtle. One of the fields in the query's anonymous type did not exist in MyType. I discovered this while I was creating a view model to use in this method.

For what it's worth, the view model works just fine. Eg:

public IQueryable<MyViewModel> GetFooList(int parameter)
{
    var query = (from x in dbContext.TableX
                 join y in dbContext.TableY on x.our_id equals y.our_id
                 join z in dbContext.TableZ on y.our_id equals z.our_id
                 join a in dbContext.TableA on z.other_id  equals a.other_id 
                 where x.their_id == parameter
                 select new MyViewModel()
                 {
                    field1 = x.field1,
                    field2 = y.field2,
                    field3 = z.field3 //<= this field did not exist in MyType
                 }
                 ).AsQueryable();

    return query;
}