CODEWITHSUNDEEP
c#asp.netlinqef-code-first
99823
99823

Reputation: 2457

Code First Entity Framework Linq Statement Returning Missing Records

I have the following query:

orderMessageEmail.MessageChain = DbContext.Current
    .Messages
    .Where(c => 
        c.OrderId == orderMessageEmail.OrderId && 
        c.IsPublic && 
        c.MessageId != orderMessageEmail.MessageId && 
        c.MessageId < orderMessageEmail.MessageId
    )
    .Select(c => new OrderMessageChain()
    {
        CreateDateTime = c.CreateDateTime,
        MessageId = c.MessageId,
        Message = c.MessageData,
        UserFirstName = c.User.FirstName,
        UserLastName = c.User.LastName,
        CustomerFirstName = c.CustomerAccountPerson.FirstName,
        CustomerLastName = c.CustomerAccountPerson.LastName,
        SentFrom = c.SentFrom
    })
    .OrderByDescending(c => c.MessageId)
    .Take(10)
    .ToList();

The problem I'm running into is that whenever c.User is null it doesn't return ANY record for the OrderMessageChain

I'd like to just have it return UserFirstName and UserLastName as empty strings instead of completely eliminating that OrderMessageChain from the list.

Does this make sense?

One more thing..

Simply testing this query:

var t = DbContext.Current
        .Messages
        .Include("User")
        .Where(c => 
         c.MessageId < 138120 && 
         c.OrderId == 170496 && 
         c.IsPublic)
         .ToList();

When I manually execute the same query in the DB i'm shown 3 records, however t is showing zero.

I always thought Include worked as a Left Join - is that not the case?

One more thing...

OK so i think i'm starting to realize what is going on here..

I didn't realize this, but it appears that the DBA setup the DB field UserId on the Message Table to NOT be nullable, however, in the case when there isn't a user record the UserId field contains a 0 (zero) value, instead of null... argh...

I think this is leading to code first to believe it should perform an inner join instead of a left join per here

So i'm not quite sure how to fix this.. is there anyway I can force code first to somehow perform a left join on that navigation property instead of an inner?

Upvotes: 0

Views: 443

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

You should be able to select the data you need, convert it to an IEnumerable and do the mapping in-memory, something like (the untested);

orderMessageEmail.MessageChain = DbContext.Current
    .Messages
    .Where(c => 
        c.OrderId == orderMessageEmail.OrderId && 
        c.IsPublic && 
        c.MessageId != orderMessageEmail.MessageId && 
        c.MessageId < orderMessageEmail.MessageId
    )
    .OrderByDescending(c => c.MessageId)
    .Take(10)
    .Select(c => new {
        c, u = c.User, cap = c.CustomerAccountPerson
    }). 
    .AsEnumerable()
    .Select(c => new OrderMessageChain()
    {
        CreateDateTime = c.c.CreateDateTime,
        MessageId = c.c.MessageId,
        Message = c.c.MessageData,
        UserFirstName = c.u == null ? "" : c.u.FirstName,
        UserLastName = c.u == null ? "" : c.u.LastName,
        CustomerFirstName = c.cap == null ? "" : c.cap.FirstName,
        CustomerLastName = c.cap == null ? "" : c.cap.LastName,
        SentFrom = c.c.SentFrom
    })
    .ToList();

Upvotes: 1

Related Questions