CODEWITHSUNDEEP
c#.netentity-frameworkef-code-first
dparsons
dparsons

Reputation: 2866

Creating Fluent Relationship not based on primary key(s)

I have a trivial SQL Query that I am trying to use with the Fluent API and Entity Framework but I am having a rough go of it. Here is the query:

 SELECT     
  tbl1.Column,      
  tbl2.Column,  
  tbl3.Column,
  tbl4.Column
 FROM dbo.Table1 tbl1  
 INNER JOIN dbo.Table2 tbl2 ON tbl1.[Table 2 ID FK] = tbl2.ID
 LEFT JOIN dbo.Table3 tbl3  ON tbl3.[NonKeyColumn] = tbl2.[NonKeyColumn]
 LEFT JOIN dbo.Table4 tbl4 ON tbl4.[Table1FK] = tbl1.ID

My problem is the join on Table2 and Table3; the columns aren't the primary keys of either table and are named completely different in both tables. My relationships between tables are this:

Table1 << M:1 > Table2

Table3 << M:1 > Table2

Table4 << M:M >> Table 2

How can I write a relationship on Table2 that will join in Table3 on two given columns?

I thought this was a step in the right direction (defined on Table1):

 this.HasMany(e => e.Table2)
     .WithOptional(e => e.Table1)
     .HasForeignKey(tlb2 => tbl2.Column)

But I hit a dead end since I can't associate the column in Table1 to the column in Table2. Thanks!

Upvotes: 0

Views: 156

Answers (2)

dparsons
dparsons

Reputation: 2866

As Ladislav pointed out you can't establish a relationship based on non-key columns but this situation isn't necessarily impossible to accomplish in EF...it is just very messy. To achieve what I wanted to do using only LINQ and EF the query ended up, basically, having a structure similar to this:

 var values = context.Table1.Select()
                        .GroupJoin(
                                    context.Table2.Select(),
                                    detail => detail.ID,
                                    entry => entry.ID,
                                    (detail, entry) =>
                                        new { //select columns })
                                     .DefaultIfEmpty()
                                     .GroupJoin(
                                            context.Table3.Select(),
                                            entry => entry.Id,
                                            check => check.NonKeyColumn,
                                            (entry, check) => new {//select additional columns})
                                     .DefaultIfEmpty()
                                     .GroupJoin(
                                            context.Table4.Select(),
                                            entry => entry.ID,
                                            transactions => transactions.ID,
                                            (entry, transactions) => new {//select additional columns})
                                     .DefaultIfEmpty();

Using GroupJoins I am able to get the LEFT JOIN behavior I needed (.Join does an INNER JOIN) and got the dataset back that I expected. That being said I ultimately abandoned this approach and simply stood up a view reducing my call to just:

  context.ViewTable.Select...

Which just goes to show you that just because you can do something doesn't necessarily mean you should. ;]

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364259

You cannot write such relationship. That would require NonKeyColumn to be unique key in Table2 but EF doesn't support unique keys yet => EF doesn't support relations based on non-primary keys.

Upvotes: 1

Related Questions