Expose one-to-many as one-to-zero/one

Question

I'm trying a model-first approach on an existing database. I cannot alter the database scheme in any way.

This database has an interesting set of relationships: User has a one-to-many relationship with Address. However, address has an 'IsActive' field and for any given user there is only one address that is active.

I'm looking for a way so make entity framework understand this, so that developers have the relationship in their model as if it were a one-to-zero/one relationship: user.Address.Line1 .

I need a real navigation property here. I am not just trying to avoid having to repeat user.Addresses.SingleOrDefault(c => c.IsActive).Line1 every time. Real navigation properties allow for more complex scenarios, like lazy and or eager loading from the database, or filtering: usaCust = customers.Where(c => c.Address.Country == "USA");.

Answer 1

You could simply add an ActiveAddress property to your entity and use it to get/set the appropriate address as active.

This will effectively give each user zero or one ActiveAddresses, which can be set to a member of the Addresses collection, or set to null. Only one Address will be active at a time.

public class User
{
    [Key]
    public int ID { get; set; }

    public virtual List<Address> Addresses { get; set; }

    [NotMapped]
    public Address ActiveAddress
    {
        get { return Addresses == null ? null : Addresses.Where(t => t.IsActive).FirstOrDefault(); }
        set
        {
            if (Addresses != null)
            {
                //set all addresses inactive
                Addresses.ForEach(t => t.IsActive = false); 
                //set specified address as active if not null                
                if(value != null)
                    value.IsActive = true;
            }
        }
    }
}

public class Address
{
    [Key]
    public int ID { get; set; }

    public string FullAddress { get; set; }

    public bool IsActive { get; set; }

    public int UserID { get; set; }

    [ForeignKey("UserID")]
    public virtual User User { get; set; }
}

To query this, you would need to enumerate and use Linq to Objects before referencing your ActiveAddress field, since it does not exist in the db. If you have a large number of records in your customers and/or addresses table this may not be feasible.

var usaCustomer = c.Customers.ToList().Where(t => t.ActiveAddress.Country == "USA").FirstOrDefault();

To use Linq to Entities, the only way I can currently think of is to query your collection through the address dbset, which is backwards from what you would like to do, but will still execute on the db side.

var usaCustomer = c.Customers.Where(t => t.Addresses.Any(a => a.IsActive && a.Country == "USA")).FirstOrDefault();

Answer 2

You intend to have a semantic validation for addresses. You could implement a method which expects a DateTime, and return the result of SingleOrDefault for the given query, like this:

public String getFirstLine(DateTime date) {
    return this.Addresses.SingleOrDefault(c => c.FromDate < date && c.ToDate > date).Line1
}

You will be able to use it, like this:

user.getFirstLine(DateTime.Now)

Note, that the method supports other dates as well, if you happen to intend to test for other dates as well.

Answer 3

Jan, Try to use Dynamic Global Filters for Entity Framework

https://github.com/jcachat/EntityFramework.DynamicFilters

Answer 4

Thanks for all the discussing and proposed solutions. They were really helpful and will probably work for 90% of people that have a similar issue.

If you can alter the database and do not need to pass in parameters, E-Bat suggested making proper updatable views with proper relationships and using those instead.

If you don't need eager loading, create a [NonMapped] computed property or helper method.

We ended up with a different solution, I cannot post all technical details of our eventual solution as it's about 1k code lines long...

Anyways, we really wanted to model the 1-many relationship as a 1-1 (for a given time) in a way so that we could eagerly load the relationship, lazy load it, or use it in a database query (filter on CurrentAddress etc).

First, we created a property on User of type Address, and added the 'NotMapped' attribute:

public class User{

  public string Name { get; set; }  //simple property

  public virtual ICollection<Address> Addresses { get; set;} //normal navigation collection

  [NotMapped] public virtual Address CurrentAddress { get; set;} //We added this

}

Then, we had a look at how you would normally get this address property filled in:

var users = from user in dbo.Users
            join address in dbo.Addresses.Where(a => a.IsActive)
            on user.Id equals address.UserId
            select new User{
              Name = user.Name
              Addresses = user.Addresses
              CurrentAddress = address
            };

We made a fluent API so we can define this join just once in a way similar to how you'd use EF fluent API to define normal relationships against the ModelBuilder.

this.Entity<User>()
  .HasJITRelationship(u => u.CurrentAddress)
  .From(dbo => dbo.Addresses
  .Where(a => a.IsActive)
  .On(u => u.Id)
  .Equals(a => a.UserId);

Lastly, suppose we have:

var usaUsers = dbo.Users.Where(c => c.CurrentAddress.Country == "USA");
foreach(var user in usaUsers.ToList()) { //...

This IQueryable is executed only when the ToList() is called. At that point, the expression tree of the usaUsers IQueryable, contains the info that the CurrentAddress join is needed.

We actually made our DBContext a bit smarter. Instead of dbo.Users returning a normal DBSet(), we're actually returning a DBSet with some injected logic. Based on this particular usage (usa-users example), we investigate the expression tree and swap out (at runtime) the normal use of dbo.Users, for a new IQueryable where we inject the 'join', using an ExpressionVisitor.

Basically, since we defined the join once using the fluent API, and we know the usage (IQueryable.Expression investigation), we can inject the join 'Just-In-Time' on a case-by-case bases.

I'm leaving out about 900 other lines of technical details...