Entity Framework - Conditionally pull data from a primary table and a history table in one query

Question

I have the following 2 tables defined in a SQL Server database:

  CREATE TABLE [dbo].[FinancialItem](
    [Id][bigint] identity(1,1) NOT NULL,
    [TranId] [int] NOT NULL,
    [Installment][int] NOT NULL,
    [Amount][decimal](16,2) NOT NULL
   CONSTRAINT [FinancialItem0] PRIMARY KEY
   (
     [Id] ASC
   )

  CREATE TABLE [dbo].[FinancialItemHistory](
    [Id][bigint] NOT NULL,
    [TranId] [int] NOT NULL,
    [Installment][int] NOT NULL,
    [Amount][decimal](16,2) NOT NULL
   CONSTRAINT [FinancialItemHistory0] PRIMARY KEY
   (
     [Id] ASC
   )

I am using Entity Framework 4.x with POCO entities.

The Entity Framework code does not write any data to the database, it is simply there to read data created by another service. What I would like to do is have one entity represent BOTH of these tables if possible. The FinancialItem and FinancialItemHistory tables have the same structure. When a FinancialItem gets paid off, the record moves from the FinancialItem table to the FinancialItemHistory table (which keeps the same Id). Is there a way in Entity Framework to conditionally pull in the FinancialItemHistory records if I am including paid off items? Should I create a View in the database that essentially does this:

SELECT Id, TranId, Installment, Amount, 0 IsHistorical
FROM FinancialItem
UNION ALL
SELECT Id, TranId, Installment, Amount, 1 IsHistorical
FROM FinancialItemHistory

I would like to avoid using a view if possible because more times than not I will simply want to pull the unpaid items that reside in the FinancialItem table. I tried having both entities derive from the same interface (IFinancialItem) and then have Entity Framework query that interface, but it appears that Entity Framework does not support mapping to complex types within the context of an IQueryable.

usr usr · Accepted Answer · 2012-04-24T20:05:08

In entity framework, you do a union all using the Concat method. If you want to union two different types, you need to convert both to a common type first like this:

var table1 = ...;
var table1 = ...;
var unionAll = table1.Select(x => new { x.ID, ... }).Concat(table2.Select(x => new { x.ID, ... }));

Entity Framework - Conditionally pull data from a primary table and a history table in one query

1 Answers