EF 6 Code First 1 to 1 Mapping, avoiding a seemingly needless LEFT JOIN

Question

Update: I should have said I'm doing database-first, and have inherited a database with some bad design. The schema is fixed and cannot be changed.

I have a Call table, and a Message table. The idea is that a Message can be created from a Call or from somewhere else (maybe a web site). So a Call can have zero or one Messages, and a Message can have zero or one Calls. It's basically a 1:1.

The issue comes into play when I check the generated query. The way I have it configured, EF always does a LEFT JOIN on Message even when I just want to SELECT * FROM Call.

Is there another way to configure an optional 1:1 relationship without changing the schema that avoids the LEFT JOIN? My real database has millions of records and needlessly joining is surely going to affect performance.

Schema with sample data:

CREATE TABLE dbo._Call
(
    CallId int IDENTITY(1,1) NOT NULL,
    FromNumber varchar(100) NOT NULL,
    ToNumber varchar(100) NOT NULL,
    CONSTRAINT PK__Call PRIMARY KEY CLUSTERED (CallId ASC)
)
GO

CREATE TABLE dbo._Message
(
    MessageId int IDENTITY(1,1) NOT NULL,
    CallId int NULL,
    AudioUrl varchar(100),
    CONSTRAINT PK__Message PRIMARY KEY CLUSTERED (MessageId ASC)
)
GO

INSERT INTO _Call (FromNumber, ToNumber) values ('+15551111111', '+15550000000');
INSERT INTO _Call (FromNumber, ToNumber) values ('+15552222222', '+15550000000');
SELECT @@identity
INSERT INTO _Message (CallId, AudioUrl) values (@@identity, 'https://from-a-call');
INSERT INTO _Call (FromNumber, ToNumber) values ('+15553333333', '+15550000000')
INSERT INTO _Message (CallId, AudioUrl) values (NULL, 'https://from-somewhere-else');

EF Model:

public class Call
{
    public int Id { get; set; }
    public string FromNumber { get; set; }
    public string ToNumber { get; set; }

    public Message Message { get; set; }
}

public class Message
{
    public int Id { get; set; }
    public string AudioUrl { get; set; }

    public Call Call { get; set; }
}

EF Mapping:

public class CallMap : EntityTypeConfiguration<Call>
{
    public CallMap()
    {
        var builder = this;

        builder.ToTable("_Call");

        // columns
        builder.Property(t => t.Id).HasColumnName("CallId");

        // relationships
        builder.HasOptional(c => c.Message)
            .WithOptionalPrincipal(m => m.Call)
            .Map(m => m.MapKey("CallId"));
    }
}

public class MessageMap : EntityTypeConfiguration<Message>
{
    public MessageMap()
    {
        var builder = this;

        builder.ToTable("_Message");

        // columns
        builder.Property(t => t.Id).HasColumnName("MessageId");
    }
}

My Unit Test:

var query = from c in context.Calls orderby c.Id descending select c;

var result = query.FirstOrDefault();

Assert.IsNotNull(result);

And the generated query with the LEFT JOIN:

SELECT TOP (1) 
    [Extent1].[CallId] AS [CallId], 
    [Extent1].[FromNumber] AS [FromNumber], 
    [Extent1].[ToNumber] AS [ToNumber], 
    [Extent2].[MessageId] AS [MessageId]
    FROM  [dbo].[_Call] AS [Extent1]
    LEFT OUTER JOIN [dbo].[_Message] AS [Extent2] ON ([Extent2].[CallId] IS NOT NULL) AND ([Extent1].[CallId] = [Extent2].[CallId])
    ORDER BY [Extent1].[CallId] DESC

As soon as you decide you want the data and in context.Calls.Include(c => c.Message), EF adds another LEFT JOIN:

SELECT TOP (1) 
    [Extent1].[CallId] AS [CallId], 
    [Extent1].[FromNumber] AS [FromNumber], 
    [Extent1].[ToNumber] AS [ToNumber], 
    [Extent3].[MessageId] AS [MessageId], 
    [Extent3].[AudioUrl] AS [AudioUrl], 
    [Extent3].[CallId] AS [CallId1]
    FROM   [dbo].[_Call] AS [Extent1]
    LEFT OUTER JOIN [dbo].[_Message] AS [Extent2] ON ([Extent2].[CallId] IS NOT NULL) AND ([Extent1].[CallId] = [Extent2].[CallId])
    LEFT OUTER JOIN [dbo].[_Message] AS [Extent3] ON [Extent1].[CallId] = [Extent3].[CallId]
    ORDER BY [Extent1].[CallId] DESC

Have you actually compared execution plans on SQL to see if it makes any difference performance wise? — Bradley Uffner, Feb 24 '17 at 02:27
This is not a 1 to 1 relationship. See this answer : http://stackoverflow.com/a/22237880/6347013 — Kinetic, Feb 24 '17 at 02:28
First, you should really have a foreign key set up in the DB schema to do this. Second, how would you get the related information without a join? Third, if you have a foreign key relationship on a primary key, a join is really cheap. — Eris, Feb 24 '17 at 02:28
@Eris (1) I agree, the schema is bad, but it's fixed and cannot be changed. (2) The fact is that I don't want the related information UNLESS I `.Include(c=>c.Message)` or use it in a `where` clause. (3) Sure it's cheap, but it's not free, especially if I don't want it. Maybe I'm worrying too much about sub-optimization. — Langdon, Feb 24 '17 at 02:33
@BradleyUffner No, but I would imagine it must. In my real code, there are actually two of these relationships. There are over a million calls, and a hundreds of thousands of the others. I can't imagine two unnecessary left joins will not make a difference in performance. — Langdon, Feb 24 '17 at 03:01
I'm pretty sure the bigger problem is the null semantics. https://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbcontextconfiguration.usedatabasenullsemantics(v=vs.113).aspx — Eris, Feb 24 '17 at 08:12

score 0 · Answer 1 · answered Feb 24 '17 at 02:39

0

This is because you are designing the class so that a Message belongs to a call. If you were to try to access the message property from the Call class, you would expect to know if a message exists or not. All setting the relationship as optional does is allow you to use a LEFT join over a Join

By Declaring the property as virtual you should be able to use lazy loading to speed up your initial query. What would happen is that you would not load the message class unless you explicitly ask for it in the code.

answered Feb 24 '17 at 02:39

mfreedm52

161
10

I'm trying to optimize performance. Lazy loading is the complete opposite of what I'm looking for. =[ – Langdon Feb 24 '17 at 02:43
FYI, the `LEFT JOIN` still occurs when `Call` and `Message` are marked as `virtual`. – Langdon Feb 24 '17 at 03:14
you could try making Message Nullable in the class. its doing a left join but still expecting that Message is Not null, so it must not be picking up that you are requesting this relationship to be optional. – mfreedm52 Feb 24 '17 at 13:36

EF 6 Code First 1 to 1 Mapping, avoiding a seemingly needless LEFT JOIN

1 Answers1