CODEWITHSUNDEEP
c#.netsqllinq
Vlad Bezden
Vlad Bezden

Reputation: 89527

LINQ with multiple left join and where clause

I have following query, and I am converting it to LINQ.

select acq.ACQPub as Prev_ACQPub
     , ve.CompanyID
         , ve.EntityID
         , ve.RoundID
         , ve.EntityName
         , ve.Date
         , ve.RoundTypeCode
         , ve.EventType
         , ve.PostValue_DJVS
         , ve.PostVal
         , ve.PreVal
         , fin.FinanceStat
    from ValuationEvents_PIT_New as ve
    left join Acq_PublicDummy as acq
    on ve.EntityID = acq.EntityID
    left join FinStat_New as fin
    on ve.EntityID = fin.EntityID
    where ve.EventType in('ACQ','LBO')
      and acq.ACQPub is null

I wanted to double check if I've done it right or there is a better way of doing it.

Here is my code:

return (from ve in valuationEvents where ve.EventType == EventTypes.Acq || ve.EventType == EventTypes.Lbo
                          join acq in acqPublicDummies on ve.EntityId equals acq.EntityID into veAcq
                          from x in veAcq.DefaultIfEmpty() where x != null && x.ACQPub == null
                          join fin in finStats on ve.EntityId equals fin.EntityID into veFin
                          from y in veFin.DefaultIfEmpty()
                          select new AcqResearch
                                     {   PrevAcqPub = x == null ? null : x.ACQPub,                                          
                                         EntityId = ve.EntityId,
                                         CompanyId = ve.CompanyId,
                                         RoundId = ve.RoundId,
                                         Date = ve.Date,
                                         RoundTypeCode = ve.RoundTypeCode,
                                         EventType = ve.EventType.ToString(),
                                         PostValueDjvs = ve.PostMoneyValue,
                                         PostVal = ve.PostVal,
                                         PreVal = ve.PreVal,                                             
                                         FinanceStat = y == null ? null : y.FinanceStat
                                     }).ToList();

Since result will be used > 1 times I am returning List instead of IEnumerable.

Also I can't run SQL and compare result of it with LINQ result, since query above runs against of Raw data and LINQ is running after data calculations and additional cleansing process. So there is no way for me to compare query result with my LINQ result. I just need to rely that logic is correct. What is the same is SQL logic and LINQ logic.

Thank you very much for your help and feedback!

Upvotes: 0

Views: 3577

Answers (2)

saus
saus

Reputation: 2174

If you want to verify that the query is the same, you can look at the SQL that is generated from the linq. there are a few ways of doing this:

  • Use SQL Profiler
  • Paste the query into LinqPad and view the SQL tab
  • Set the Log property on your datacontext

You can then compare the two SQL queries and check for differences.

As an aside, in your case the only things I would change are style related - I would move all of the where clauses to just above the select clause, so it is easier to see what filter you are applying. Also, the line

PrevAcqPub = x == null ? null : x.ACQPub

Seems like it may as well be

PrevAcqPub = null

Since you have already filtered out all x that are null and that have x.ACQPub != null (Same goes for the SQL query).

Upvotes: 2

dahlbyk
dahlbyk

Reputation: 77520

Your solution looks correct to me

Upvotes: 1

Related Questions