Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Linq Left Out Join with Entity Framework Order By

Posted on 2010-09-14
2
Medium Priority
?
805 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

773 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