Solved

Linq Left Out Join with Entity Framework Order By

Posted on 2010-09-14
2
772 Views
Last Modified: 2013-11-11
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.

0
Comment
Question by:weimha
2 Comments
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 500 total points
ID: 33679397
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
 

Author Comment

by:weimha
ID: 33747893
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now