Linq Left Out Join with Entity Framework Order By

I have the following tables:

BookOfBusiness { Id, Name}
OfficeBookOfBusinessAssignments { Id, OfficeId, EffectiveDate, ExpirationDate}

I need to query for a Book Of Business by Id and get the first effective date and last expiration date.

So If my data looked like this:

BookOfBusiness
Id         Name
567      Book1
789      Book2

OfficeBookOfBusinessAssignment
Id         EffectiveDate          ExpirationDAte
576      2010-01-01             2010-05-01
576      2010-05-01             NULL

And I search by Id 567

I want back 576 Book1  2010-01-01  NULL

Using Entity Framework, I have the following working for 576

 var item = context.BookOfBusinesses
                    .GroupJoin(context.OfficeBookOfBusinessAssignments,
                        a => a.BookOfBusinessId,
                        b => b.BookOfBusinessId,
                        (b, g) => new { b, g })
                    .SelectMany(z => z.g.DefaultIfEmpty(), (z, b) => new { x = z.b, y = b })
                    .Where(z => z.x.BookOfBusinessId == bookOfBusinessId).ToList();

                if (item != null && item.Count() > 0)
                {
                    result = new BookOfBusiness();
                    result.Id = item[0].x.BookOfBusinessId;
                    result.Name = item[0].x.Name;
                    
                    var firstItem = item.OrderBy(b => b.y.EffectiveDate).FirstOrDefault();
                    var lastItem = item.OrderByDescending(b => b.y.EffectiveDate).FirstOrDefault();

                    if (firstItem != null)
                        result.DisplayOpenDate = firstItem.y.EffectiveDate;
                    if (lastItem != null)
                        result.DisplayCloseDate = lastItem.y.ExpirationDate;
                }
                else
                    result = null;

Open in new window


But it doesn't work if search for Book of Busienss 789 because there are no records in OfficeBookOfBusienssAssignement, so Effective date is null when I try to order by it.  

There has got to be a better way to get this data.  

Any help would be appreciated.

Thanks.

weimhaAsked:
Who is Participating?
 
Gautham JanardhanConnect With a Mentor Commented:
do we have a navigation property in BookOfBusinesses for OfficeBookOfBusinessAssignments ?

then we can do something like this

var record =
 from c in context.BookOfBusinesses
        from p in c.OfficeBookOfBusinessAssignments .DefaultIfEmpty()
select new
{
c.Id,
c.EffectiveDate,
ExpirationDate = (p == null ? null : p.ExpirationDate)
}
0
 
weimhaAuthor Commented:
I do have a navigation property.
I've attached what I ended up doing.  

 

 var item = (from b in context.BookOfBusinesses
                           join o in context.OfficeBookOfBusinessAssignments on b.BookOfBusinessId equals o.BookOfBusinessId into bo
                           from sub in bo.DefaultIfEmpty()
                           where b.BookOfBusinessId == bookOfBusinessId
                           select new { b.BookOfBusinessId, 
                                        b.Name, 
                                        ExpirationDate = (sub == null ? null : sub.ExpirationDate),
                                        EffectiveDate = (sub == null ? DateTime.MinValue : sub.EffectiveDate)}).ToList();

                if (item != null && item.Count() > 0)
                {
                    result = new BookOfBusiness();
                    result.Id = item[0].BookOfBusinessId;
                    result.Name = item[0].Name;

                    DateTime effectiveDate = item.OrderBy(b => b.EffectiveDate).First().EffectiveDate;
                    if (effectiveDate == DateTime.MinValue)
                        result.DisplayOpenDate = null;
                    else
                        result.DisplayOpenDate = effectiveDate;
                    result.DisplayCloseDate = item.OrderByDescending(b => b.EffectiveDate).First().ExpirationDate;

                }
                else
                    result = null;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.