What's wrong with this LINQ

Question

I have a simple DTO that I'm trying to return via RIAServices and I keep getting this error: "Load operation failed for query. Unable to create constant value of type 'System.Object'. Only primitive types are supported in this context.

Here's the query method:

public IQueryable<SupportDbDTO> GetDbsRelatedToModel(string modelDtoId)
{
    IQueryable<SupportDb> query; 

    var key = new Guid(modelDtoId);
    var siteId = _context.SupportDbs.Single(db => db.Id.Equals(key)).SiteDbId ?? Guid.Empty;

    query = _context.SupportDbs
       .Where(db => !db.Id.Equals(key))
       .Where(db => db.DBType == DB.CATALOG_DB || db.DBType == DB.CATALOG_SCHEMA_DB || db.DBType == DB.MODEL_DB)
       .Where(db => db.SiteDbId.Equals(siteId))
       .OrderBy(db => db.DBName);

    return query.AsEnumerable()
        .Select(db => new SupportDbDTO
        {
         Id = db.Id.ToString(),
         DBName = db.DBName,
         DBType = db.DBType,
         PlantRoot = db.PlantRoot,
         DBSize = db.DBSize ?? 0,
         ModifiedDate = db.ModifiedDate ?? DateTime.Now       
        }).AsQueryable();
}

I got it to work by using == instead of Equals. This was the offending one:

query = _context.SupportDbs
    .Where(db => db.Id != key)
    .Where(db => db.DBType == DB.CATALOG_DB || db.DBType == DB.CATALOG_SCHEMA_DB || db.DBType == DB.MODEL_DB)
    .Where(db => db.SiteDbId == siteId)
    .OrderBy(db => db.DBName);

thanks Sunit

Answer 1

1. Check if SupportDbDTO is serializable
2. instead of returning IQuerable return an array(SupportDbDTO[])

Answer 2

I assume you are using Entity Framework? The LINQ provider for Entity Framework is going to take your LINQ query and directly translate it into SQL. LINQ to SQL probably does the same thing but I've never used it.

So the EF LINQ provider doesn't know how to turn with Object.Equals() into a chunk of SQL.

The simplest answer is to do what you are already doing in other places, convert the query into an enumerable with AsEnumerable(), then from that point on its no longer an EF query but a LINQ to Objects query, and the whole of LINQ will now work, at the cost of having to pull the whole query out of the database into memory.

But in this particular case, just using == looks like it did the trick for you.