Solved

Linq Left Out Join with Entity Framework Order By

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to change the view engine in visual studio 2015 enterprises edition 7 48
Hey!! 5 33
Hey!!!! 1 21
SQL Help 27 45
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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