CODEWITHSUNDEEP
c#asp.net-mvclinq
Jeff
Jeff

Reputation: 2313

Casting derived class to base class using LINQ to Entities

I have two database tables, one to hold completed items and another to hold incomplete items. Both tables are identical in structure. There are some cases where I would like to quest just one of these tables, but other cases where I would want to query the concatenation of both tables.

Classes

public abstract class SomeBase
{
    public int Id {set;get;}
    public string Item1 {set;get;}
    public string Item2 {set;get;}
}
public class A : SomeBase
{
}
public class B : SomeBase
{
}

Mapping (Fluent API)

public class SomeDatabase : DbContext
{
    public DbSet<A> As {set;get;}
    public DbSet<B> Bs {set;get;}
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<A>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable("ATable", "SomeSchema");
        }
        modelBuilder.Entity<B>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable("BTable", "SomeSchema");
        }
    }
}

Using this setup, I can query both tables just fine individually like so

var db = new SomeDatabase();
var a = db.As;
var b = db.Bs;

When trying to combine the two, I can't quite figure out the syntax. One solution in the answer below involves casting it using .AsEnumerable but that isn't quite what I'm looking for since it evaluates immediately.

db.As.AsEnumerable().Concat<SomeBase>(db.Bs);
db.As.Cast<SomeBase>().Concat(db.Bs.Cast<SomeBase>());

How can I concatenate two derived classes that are identical on the database site?

EDIT: At the lowest level, I am getting these errors

db.As.Cast<SomeBase>();
Unable to cast the type 'Test.Models.A' to type 'Test.Models.SomeBase'. LINQ to Entities only supports casting Entity Data Model primitive types.

db.As.OfType<SomeBase>();
'Test.Models.SomeBase' is not a valid metadata type for type filtering operations. Type filtering is only valid on entity types and complex types.

Related question: How to combine 2different IQueryable/List/Collection with same base class? LINQ Union and Covariance issues

Upvotes: 4

Views: 4681

Answers (3)

Tz_
Tz_

Reputation: 2949

Simply define a 

DbSet<SomeBase> Bases { get; set;}

property to access all instances of the base class. The framework should combine the query the right way (union) to include the instances from both tables.

For more details check out e.g. this article: http://weblogs.asp.net/manavi/archive/2011/01/03/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-3-table-per-concrete-type-tpc-and-choosing-strategy-guidelines.aspx

(You use the TPC inheritance strategy)

Upvotes: 2

Tormod
Tormod

Reputation: 4573

How about: 

var concatenatedSet = db.As.Local.OfType<SomeBase>().Concat(db.Bs.Local.OfType<SomeBase());

Upvotes: 1

maxlego
maxlego

Reputation: 4914

maybe there is some more elegant way, but union should do it i guess:

db.As.Select(x => new { x.Id, x.Item1, x.Item2 } )
.Union(db.Bs.Select(x => new { x.Id, x.Item1, x.Item2 }));

if you want to include some fields from As and some fields from Bs then it should look like:

db.As.Select(x => new { x.Id, x.Item1, x.Item2, x.Afield, Bfield = null } )
.Union(db.Bs.Select(x => new { x.Id, x.Item1, x.Item2, AField = null, x.Bfield }));

Upvotes: 1

Related Questions