CODEWITHSUNDEEP
entity-framework-6
Phillippe Santana
Phillippe Santana

Reputation: 3116

How to map redundant relationship in EF6

I have a Quote object, with a collection of QuoteAnswer objects. I also want a shortcut to the latest QuoteAnswer. So I modeled (irrelevant code ommitted for brevity):

public class Quote
{
    public int Id { get; set; }
    public ICollection<QuoteAnswer> Answers { get; set; }

    public QuoteAnswer LatestAnswer { get; set; }
    public int LatestAnswerId { get; set; }
}

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

    public Quote Quote { get; set; }
    public int QuoteId { get; set; }

    /* Did not map this, not interested/not needed
     * public Quote LastAnswerFor { get; set; }
     * public int LastAnswerForId { get; set; }
    */
}

That's beacuse I want to be able to do this:

var quotes = context.Quotes
    .Include(x => x.LatestAnswer)
    .ToList();

Instead of this:

var quotes = context.Quotes
    .Include(x => x.Answers)
    .ToList();

foreach (var q in quotes)
{
    var latestAnswer = q.Answers.OrderByDescending(x => x.Date).FirstOrDefault();
}

Which would obviously force me to load unecessary data.

The Problem

When I try to map this do database code (add a migration), I get a new property I don't know where it's comming from.

Generated migration code (parts ommitted for brevity):

CreateTable(
    "dbo.QuoteAnswer",
    c => new
        {
            Id = c.Int(nullable: false),
            QuoteId = c.Int(nullable: false),
            QuoteId1 = c.Int(),
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.Quote", t => t.QuoteId)
    .ForeignKey("dbo.Quote", t => t.QuoteId1)
    .Index(t => t.QuoteId)
    .Index(t => t.QuoteId1);

AddColumn("dbo.Quote", "LatestAnswerId", c => c.Int());
CreateIndex("dbo.Quote", "LatestAnswerId");
AddForeignKey("dbo.Quote", "LatestAnswerId", "dbo.QuoteAnswer", "Id");

What's that QuoteId1 thing? I get the QuoteId, but I don't recognize QuoteId1.

How can I achive this mapping? Is this even supported in EF6?

Upvotes: 1

Views: 73

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205589

First, it's possible. The explicit FK property should be removed:

public class Quote
{
    public int Id { get; set; }
    public string Data { get; set; }
    public ICollection<QuoteAnswer> Answers { get; set; }

    public QuoteAnswer LatestAnswer { get; set; }
}

and the new relationship should be mapped with fluent API:

modelBuilder.Entity<Quote>()
    .HasOptional(e => e.LatestAnswer)
    .WithOptionalDependent()
    .Map(m => m.MapKey("LatestAnswerId"));

But I won't recommend you doing it because it would introduce a lot of maintenance problems - aside of the obvious need to keep it up-to-date, it would create circular FK dependency, so basically all CUD operations would be problematic (if working at all).

I think you are trying to solve the "loading unnecessary data" problem is a wrong way. You can achieve the same goal by using simple projection:

var quotesWithLatestAnswer = context.Quotes
    .Select(q => new { Quote = q, LatestAnswer = q.Answers.OrderByDescending(a => a.Date).FirstOrDefault() })
    .ToList();

Note that the code inside Select will be translated to SQL and executed in the database, returning only the data needed.

To return the latest answer as part of your entity, you can make mark it as unmapped:

public class Quote
{
    public int Id { get; set; }
    public string Data { get; set; }
    public ICollection<QuoteAnswer> Answers { get; set; }
    [NotMapped]    
    public QuoteAnswer LatestAnswer { get; set; }
}

and use a combination of LiNQ to Entities (SQL) and LINQ to Objects query:

var quotes = context.Quotes
    .Select(q => new { Quote = q, LatestAnswer = q.Answers.OrderByDescending(a => a.Date).FirstOrDefault() })
    .AsEnumerable() // end of db query
    .Select(qi => { qi.Quote.LatestAnswer = qi.LatestAnswer; return qi.Quote; })
    .ToList();

This way you'll have clean and easy to maintain relational database model as well as efficient retrieval of the data needed.

Upvotes: 1

Related Questions