In my last post I talked about a mapping option in Entity Framework called table splitting. Table splitting allows you to take a single physical table and split it into multiple entities in your Entity Framework model. Today I will talk about another modeling option called entity splitting.

Entity splitting gives you the ability to take a single entity in your model and split it into multiple physical database tables. It is essentially the opposite of table splitting. When you query this entity in Entity Framework, it will create a query that automatically joins to the tables for you. Consider the following entity in Code First:

public class User
{
    // these fields come from the Users table
    public int UserId { get; set; }
    public string Username { get; set; }

    // these fields come from the UserProfiles table
    public string FavoriteColor { get; set; }
    public string EmailAddress { get; set; }
}

The above model stores information about a User in our system. The UserId and Username properties will be stored in the Users table, and the EmailAddress and FavoriteColor properties will be stored in the UserProfiles table. Without adding the configuration, Code First will use the default conventions to create a single table (dbo.Users) where all 4 properties would be stored. Let's change that by overriding the default conventions:

public class EntitySplittingContext : DbContext
{
    public IDbSet Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            // Map to the Users table
            .Map(map =>
            {
                map.Properties(p => new {
                    p.UserId,
                    p.Username
                });

                map.ToTable("Users");
            })
            // Map to the Users table
            .Map(map =>
            {
                map.Properties(p => new {
                    p.FavoriteColor,
                    p.EmailAddress
                });

                map.ToTable("UserProfiles");
            });
    }
}

We use two Map statements to select which properties go to the Users table and which properties go to the UserProfiles table. Entity Framework will create two tables in the database with a one-to-one relationship. Since we chose to have UserId belong to the Users table, it will treat that table as the Parent table. The UserProfiles table will also contain a UserId primary key column but it will not be an identity and will contain a foreign key reference back to the Users table.

As I mentioned before, when we query the Users entity the query that is generated for us will automatically perform a join between the two tables. Here is a query that demonstrates this behavior:

using(var db = new EntitySplittingContext())
{
    var users = db.Users.Where(u => u.Username == "Travis.Ellis").ToList();
}

-- SQL Query
SELECT 
[Extent1].[UserId] AS [UserId], 
[Extent1].[Username] AS [Username], 
[Extent2].[FavoriteColor] AS [FavoriteColor], 
[Extent2].[EmailAddress] AS [EmailAddress]
FROM  [dbo].[Users] AS [Extent1]
INNER JOIN [dbo].[UserProfiles] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[UserId]
WHERE N'Travis.Ellis' = [Extent1].[Username]

A significant benefit to using Entity Framework is that your entity model does not have to match your database model. One of the ways this is accomplished is by using Table Splitting, which is having two entities in your model map to the same physical database table.

An example of using table splitting would be if you have a Customer entity that contains details about a Customer (first name, last name, e-mail, etc.) and a separate entity to store their address. The database table might store all of this information in a single physical table, but we might not want to pull back the Address every time we want the Customer information.

To accomplish this scenario in Code First you will need the following two entity classes and DbContext configuration:

public class Customer
{
    public int CustomerID { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string EmailAddress { get; set; }

    public CustomerAddress Address { get; set; }
}

public class CustomerAddress
{
    public int CustomerID { get; set; }

    public string Line1 { get; set; }
    public string Line2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }

    public Customer Customer { get; set; }
}

public class TableSplittingContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .HasKey(pk => pk.CustomerID)
            .ToTable("Customers");

        modelBuilder.Entity()
            .HasKey(pk => pk.CustomerID)
            .ToTable("Customers");

        modelBuilder.Entity()
            .HasRequired(p => p.CustomerAddress)
            .WithRequiredPrincipal(c => c.Customer);
    }

    public IDbSet Customers { get; set; }
}

The database that gets generated from this model will have a single table in it that looks like the following:

Now when you write a query to retrieve a Customer it will only retrieve columns related to the Customer and not the address. If you retrieve a Customer with the Address it will generate a query that selects both sets of columns. Here is an example of both of those queries and the resulting SQL queries that get generated:

using(var db = new TableSplittingContext())
{
    var customers = db.Customers.ToList();
    var customersAndAddress = db.Customers.Include(c => c.Address).ToList();
}

-- Query 1
SELECT 
    [Extent1].[CustomerID] AS [CustomerID], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName]
FROM [dbo].[Customers] AS [Extent1]

-- Query 2
SELECT 
    [Extent1].[CustomerID] AS [CustomerID], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent1].[Line1] AS [Line1], 
    [Extent1].[Line2] AS [Line2], 
    [Extent1].[City] AS [City], 
    [Extent1].[State] AS [State], 
    [Extent1].[ZipCode] AS [ZipCode]
FROM [dbo].[Customers] AS [Extent1]

As you can see the first query only pulls the relevant Customer details and the second query pulls both the Customer and Address columns, all coming from a single table. This allows the queries that do not need Address information to be more efficient. In a future post I will talk about Entity Splitting, which is the opposite of table splitting in that two tables map to a single Entity in the model.

I recently worked on a project where I was calling a WCF service that returned a collection of values that had a Name and a Type. I needed to use that list to select values from another database using Entity Framework.  A typical result set from the WCF service looked like this:

var list = new List<NameAndType>
{
    new NameAndType { Name = "ABC", Type = "123" },
    new NameAndType { Name = "ABC", Type = "456" },
    new NameAndType { Name = "DEF", Type = "123" },
    new NameAndType { Name = "DEF", Type = "123" }
};

In a local database I had a table that had a composite primary key made up of Name and Type. I needed to write a query that took this list of name/type values and found matching rows in my database. This would be a simple thing to do if I had a single value as my key. If that were the case I could have written a query like this:

var list = List<string>
{
    "ABC",
    "DEF"
};
    
var items = from i in context.Items
            where list.Contains(i.Name)
            select i;

The above query will translate into a statement similar to SELECT * FROM Items WHERE Name IN ('ABC', 'DEF'). The problem is that I had two properties that I needed to predicate on. I wasn't sure how to write the query in Entity Framework but my first thought was something like the following:

var list = new List
{
    new NameAndType { Name = "ABC", Type = "123" },
    new NameAndType { Name = "ABC", Type = "456" },
    new NameAndType { Name = "DEF", Type = "123" },
    new NameAndType { Name = "DEF", Type = "123" }
};

var result = from a in context.Items
             join b in list
             on new { a.Name, a.Type } equals new { b.Name, b.Type }
             select a; 

This query threw an error complaining that only primitive types could be used. I tried a few more queries but I could not figure out a way to do this. I started to think about how I would actually accomplish this in SQL using a stored procedure and settled on using a Table-Valued Parameter (which I have blogged about before). At first I thought I was going to need to write a stored procedure and call that from Entity Framework, but after doing a little testing I was able to come up with a solution that allowed me to do this using an Entity Framework query:

DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Type"< typeof(string));

list.ForEach(l => dt.Rows.Add(l.Name, l.Type));

SqlParameter tableParameter = new SqlParameter("@Ids", SqlDbType.Structured);
tableParameter.TypeName = "dbo.NameAndType";
tableParameter.Value = dt;

var items = context.Database.SqlQuery<Item>(
    "SELECT * FROM dbo.Items items " +
    "INNER JOIN @Ids ids ON items.Name = ids.Name " +
    "AND items.Type = ids.Type", tableParameter);

The code creates a DataTable and adds a row for each item in my list of NameAndType values. It then creates a SqlParameter with SqlDbType of Structured and gives it a TypeName of dbo.NameAndType and a value of the table. I then created an Entity Framework query using the Database.SqlQuery<T> method which allows you to execute some SQL code. I select from my table and join to my table valued parameter and was able to get the items from my database.

One thing to note with this approach is that all of the items returned from the query will not be tracked by Entity Framework. If you need Entity Framework to track the entities you would have to do something similar to this:

foreach ( var item in items ) {
    db.Items.Attach(item);
}

In Summary

This concludes my post on writing an Entity Framework query that uses a table-valued parameter. If you have any questions, feel free to contact me with any questions.