Entity Framework Code First custom join

I have a Customers table, and an Addresses table. Customers can have many addresses, but they also have one which is marked as their primary address (if they have any addresses).

Is there some sort of way in code first where I can put a field in the Customer POCO called "PrimaryAddress", and use something like Customers.Include(customer => customer.开发者_运维技巧PrimaryAddress) to just pick up the address which is related to the customer AND has the primary bit set?

I presume the next best thing would be just to write a view in SQL which does the custom join?

Thanks.

I don't think this is directly possible. If you map something it will end up either as relation or column. So if you expect your customer to look like:

public class Customer
{
    ...
    public Address PrimaryAddress { get; set; }
    public ICollection<Address> Addresses { get; set; }
}

It will end up as the table:

CREATE TABLE dbo.Customers
(
    ...
    PrimaryAddress_ID INT NULL
)

Depending on Address entity definition you will either have Addresses mapped as 1:N (Customer_ID in Addresses table or M:N (junction table) relation.

This is not very good because there should also be some constraint that PrimaryAddress_ID must be from addresses related to customer. Such constrain will probably need to be checked by a trigger.

View is also not way to go unless you are building readonly application or you are ready to define INSTEAD OF triggers.

You can do it without mapping:

public class Customer
{
    ...
    public ICollection<Address> Addresses { get; set; }

    [NotMapped]
    public Address PrimaryAddress
    {
        get
        {
            return Addresses.Single(a => a.IsPrimary);
        }
        // If you need set you can implement it in similar way
    }
}

You can also define it without attribute but you need to use Ignore on OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
        .Ignore(c => c.PrimaryAddress);
}

The disadvantage of the approach is that it will always load all addresses.

In case you know that you will not need other then primary address you can use:

context.Entry<Customer(customer)
    .Collection(c => c.Addresses)
    .Query()
    .Where(a => a.IsPrimary)
    .Load();

This will load only primary address but you must load Customer first so you will have to roundrips to database.