Solved

Linq Left Out Join with Entity Framework Order By

Posted on 2010-09-14
2
773 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

943 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

6 Experts available now in Live!

Get 1:1 Help Now