Query base entity to retrieve all derived entity's data using Linq to Entities

Question

I have following classes in my model:

public class Party
{
     public int Id {get; set;}
}

[Table("Person")]
public class Person:Party
{
     public string FirstName {get; set;}
     public string LastName {get; set;}
}

[Table("Organization")]
public class Organization:Party
{
     public string Name {get; set;}
}

How can I query over Parties and return result as following PartyViewModel, using linq-to-entities Fluent API?

public PartyViewModel
{
    public int Id {get;set;}  
    public string FirstName {get;set;}  
    public string LastName {get;set;}  
    public string Name {get;set;}  
}

e.g if I have following records:

     Person Table
------------------------   
Id   FirstName  LastName
 0     John      Smith  


   Organization Table
------------------------   
Id          Name  
 1        Microsoft

I want to, the query returns:

         PartyViewModel  
---------------------------------
Id   FirstName  LastName    Name
 0     John      Smith      null
 1     null      null     Microsoft

score 1 · Accepted Answer · answered Oct 28 '14 at 10:26

Using SQL UNION should get you what you need.

var q1 = from p in db.Persons
         select new PartyViewModel {
           Id = p.Id,
           FirstName = p.FirstName,
           LastName = p.LastName,
           Name = null
         };
var q2 = from o in db.Organizations
         select new PartyViewModel {
           Id = o.Id,
           FirstName = null,
           LastName = null,
           Name = o.Name
         };

var vm = q1.Union(q2).ToList();

Query base entity to retrieve all derived entity's data using Linq to Entities

1 Answers1