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.

Solution 1

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

Solution 2

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.

Comments

[Codes with Hammer]

Nope, same-ish error as my first edit. The difference is that instead of the error being with the complex type (namespace).MyType, now it's with EFModel.MyType (where "EFModel" is the Entity Frameworks model).

[Codes with Hammer]

your suggestion to use a DTO for a strongly typed IQueryable helped me to the solution. :beer: