.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

Left Outer Join in LINQ + Entities

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :ASP.Net
 
HiI have found quite a bit of info on the net, but I can't get it to work. Here's the normal SQL query:SELECT cl.ID AS ClientID, cl.Name, cl.Surname, CLC1.PHONECODE + ' ' + CLC1.PHONENUMBER AS WorkNum, CLC2.PHONECODE + ' ' + CLC2.PHONENUMBER AS HomeNum, CLC3.PHONECODE + ' ' + CLC3.PHONENUMBER AS CellNum, CLC1.PREFERRED AS WorkNumPref, CLC2.PREFERRED AS HomeNumPref, CLC3.PREFERRED AS CellNumPref, veh.ID AS VehicleID, veh.MAKE, veh.MODEL, veh.YEAR, veh.REGNO, ws.ID AS WorkshopID, ws.JOBTYPE, ws.SERVICEADVISOR, ws.TIMEFINISHED, ws.TECHNICIAN, fs.ID as FollowupID, fs.TYPE, fs.DESCRIPTION, fs.SCHEDULEDATE, fs.WRONGNUMBERFLAG FROM CLIENTS AS cl LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC1 ON cl.ID = CLC1.CLIENTID AND CLC1.TYPE = 'W' LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC2 ON cl.ID = CLC2.CLIENTID AND CLC2.TYPE = 'H' LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC3 ON cl.ID = CLC3.CLIENTID AND CLC3.TYPE = 'C' INNER JOIN CLIENTVEHICLES AS clveh ON cl.ID = clveh.CLIENTID INNER JOIN VEHICLES AS veh ON clveh.VEHICLEID = veh.ID INNER JOIN WSBOOKINGS AS ws ON clveh.ID = ws.CLIENTVEHICLEID INNER JOIN FOLLOWUPSCHEDULE AS fs ON fs.WSBOOKINGID = ws.ID WHERE fs.ID = 2 There is a multiple join between CLIENTS and CLIENTCONTACTNUMBERS so it would return a single row of data. If you were to do a normal join on CLIENTS and C


View Complete Post


More Related Resource Links

LINQ left outer join question

  
I can do this in other ways, but am looking for the "LINQ" way of doing this: Table1: People (PersonId,SchoolId)Table2: AssignedPeople (PersonId,SchoolId,RoomId) I want to select the people in Table1 within a particular SchoolId that are not in the assignedPeople table (in a particular Room).  The SQL is: select p.* from people p left outer join assignedpeople ap on p.PersonId=ap.Person and p.SchoolId=ap.SchoolIdand ap.RoomId=@roomId where p.SchoolId=@SchoolId and ap.PersonId is null I tried this LINQ (VB,NET):dim SchoolID as guid=...dim RoomID as guid=... (from p in dc.people where p.SchoolId=SchoolIdgroup join ap in dc.AssignedPeopleon p.PersonId equals ap.PersonId and p.SchoolId equals ap.SchoolId into groupfrom g in group.DefaultIfEmptywhere g.RoomID = nothing) but I can't get the ap.RoomId=@roomId part into the right place, should be something like:group join (from ap in dc.AssignedPeople where ap.RoomId=RoomId) Any help would be appreciated,Reuven

left outer join linq

  
 Hi   I try to get this exsisting linq query to use a left outer join without success, can anyone show me how its supposed to be?   Dim product = (From f In db.tbl_Formats _ Join p In db.tbl_Products On f.FormatID Equals p.FormatID Where p.NodeId = iNodeID _ Select p, f.FormatName).SingleOrDefault   The FormatID is not always present and I therefore need a left outer join on the Formats table.   Best Regards

Left Outer Join fails to return all records

  
I have a table tblDrop that contains 1067 records for 8/31/2010 and a table tblSoftcount that has 739 records, some with bad serial numbers. I need to match all of the records in tblDrop with those in tblSoftcount. I have a query using Left outer join that has worked for the duration of the application, but has recently stopped returing the full set of records from tblDrop. We have not changed the SQL versions on the server (compatibility level 2005). Here is the query: SELECT   v.SlotNum_ID, s.SlotNum_ID, v.PTNumber, s.PTNumber, v.DropBillAmount AS [Drop], v.ReportDate, v.DateValue     FROM tbl_Drop v LEFT OUTER JOIN tblSoftCount s ON v.SlotNum_ID = s.SlotNum_ID AND v.ReportDate = s.ReportDate WHERE   s.ReportDate = '8/31/2010' AND v.DropBillAmount <> 0 AND s.Slotnum_ID IS NULL;  

Get all data - LEFT OUTER JOIN with condition

  

Hi there

I have 2 tables related,

first = WBF_EVN_evn_event(keeps all event records),
second = WBF_EVN_ptc_participant(keeps all records related to participants)

I'm trying to get all events which the current user has not attended or not replied(if ptc_pts_id = 2 then the participant has not attended OR I want to see also where ptc_pts_id is NULL)

       SELECT                  evn_id, evn_name, evn_date_start, evn_date_end, ptc_pts_id, ptc_usr_id
       FROM                    WBF_EVN_evn_event
       LEFT OUTER JOIN         WBF_EVN_ptc_participant ON evn_id = ptc_evn_id
       WHERE                   evn_deleted = 0 
       AND                     (ptc_pts_id is NULL or (ptc_pts_id = 2 AND ptc_usr_id = 24 ) )       
       AND                     evn_dom_id = 1


With this query I get up to some point good result;if there is no other

Dynamic LINQ Outer Join

  

Hey folks,

I want to do an outer join on an IQueryable without LINQ.

I have this query on all my services which differs only by one property reference. I want to abstract it away but I need to substitute the property reference with something I can pass in as an argument.

Heres the query:


var secureList = (from l in list
                join ep in meta.EntityEffectivePermissions on new { EntityId = l.AnnouncementId, EntityType = "Announcement" } equals new { EntityId = ep.EntityId.Value, EntityType = ep.Name } into tempEEP
                from eep in tempEEP.DefaultIfEmpty()
                where !SecurityState || (eep.UserId == UserId && ((int)eep.Permissions & (int)Shivam.ASL.Core.Security.Permission.List) == (int)Shivam.ASL.Core.Security.Permission.List)
                select new { EntityId = l.AnnouncementId, Permissions = eep.Permissions.GetValueOrDefault() }).Distinct();


So the l.AnnouncementId (and obviously the string that I set to EntityType but that's easy) differ. I figured I could write this query using the IQueryable Join method but the documentation on this is just awful. I have found an example on how to use it but I am unsure as how to achieve a left join effect with it. Can someone help?



Regards,
Sam


Left Outer Join

  


select A.names,B.address,C.age from A left outer join B on A.names=B.names left outer join C on C.age=B.age

select A.names,B.address,C.age from A left outer join B on A.names=B.names left outer join C on C.names=B.names

select A.names,B.address,C.age from A left outer join B on A.names=B.names left outer join C on C.address=B.address

select A.names,B.address,C.age from A left outer join B on A.names=B.names left outer join C on A.age=B.ages

how does this work, all the data in the tables A,B & C are the same, is the last query valid,bocz 'on' condition is not for the table of the left outer join


Help with LINQ left join

  

Hi,

Im having a little trouble getting the syntax right in the following LINQ query.

            var merchants =
from m in dtM.AsEnumerable()
join mp in dtMP.AsEnumerable()
on m.Field<int>("MerchantId") equals mp.Field<int>("MerchantId")
into tmpProfile
from t in tmpProfile.DefaultIfEmpty()
where m.Field<string>("Name").ToLower().Contains(searchText.ToLower()) ||
t.Field<string>("Profile").ToLower().Contains(searchText.ToLower())
select m.Field<int>("MerchantId");

The problem is the highlighted line where I'm attempting to make a decision on a field that is potentially null/empty.  Anyone know how I get round this?


Thanks


left outer join

  

Hi All,

I have one confusion. I have been told that if I am joining the two tables using the left outer join then I cannot use columns from the right side table  in where condition, otherwise it will act as a inner join.

e.g. select tr.* from trans tr LEFT OUTER JOIN detail d on tr.hprop = d.hprop where tr.hmy between 1 and 100 and d.hprop = 2 ....(will this act as inner join as I have mentioned d.hprop =2 in where condition?)

Is it correct? anf if yes then how?

 Please Help.

Thanks!

Sachin :)


Any opinions on using Projection or explicit join syntax when joining entities in Linq to SQL?

  

Folks,

I would like to know whether people use the join syntax OR projection when joining entities in LINQ to SQL queries. I have noticed that when using projection that a CROSS JOIN is in the generated SQL

e.g. 

var query =
from o in Orders
from  oi in o.OrderItems
where oi.OrderId == o.OrderId

generates SQL something like...

FROM [Customer].[Orders] AS [t0]
CROSS JOIN [Customer].[OrderItems] AS [t1]
WHERE ([t1].[OrderId] = [t0].[OrderId]) AND ([t1].[OrderId] = [t0].[OrderId])

whereas with explicit joins I get a clean inner join..

var query =
from o in Orders
join oi in OrderItems on o.OrderId equals oi.OrderId

generates..

FROM [Customer].[Orders] AS [t0]
INNER JOIN [Customer].[OrderItems] AS [t1] ON [t0].[OrderId] = [t1].[OrderId] 

I know using explicit joins over projection is frowned upon, but I am wary of those cross joins.

Just wondering what other folks preferences are when using joins in L2SQL.

Cheers

K

 

 


Linq join fields for return data

  

Hello,
  how would I join fields together?

           return (from c in storedb.Product_Categories

                         where c.Category_Name.Contains(searchText)
                         orderby c.Category_Name
                         select new {
                                    c.Cat_GUID,
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....
                          

repeating merge joins using full outer join - should be possible without sort, but cannot get the co

  
Hello,   I am trying to combine several source tables with Merge Join.  For the Moment I have three source tables all already sorted in the source query and the is sorted property with the correct keys set. (its an aggregate query with group by and sort by the key columns) Then I do a merge join between two of the tables - with a full outer join. Then I just want to do another merge join with the output of the next result. but as the merge join do not combine the key columns into one column I need to add a derived column which substitute null values on the left side of the join with values from the right side of the join. The second merge then says only the left columns of the first merge join is sorted, but not the comined columns. If i Replace with a derived column the left side primary key columsn with the combinded key columns the next merge join says the input is not sorrted? But the output must be sorted. So how do I get the next merge join working without the ssis sort transformation? the data is sorted, but the component does not recognize the sorted data? I do not want to use (can use) the sort because there are flowing >= 500 Mio. records through the pipeline. Any help appreciated.   Hanneshttp://www.hmayer.net/

Serializing Linq to Entities for use in a REST Service

  
What would be the best approach to send data retrieved from Linq to Entities over a rest service? For example, if you have a Linq to Entities class, say Products from the Northwind database. I want to return a subset of the Products (based on a query on the model) back through a service. First a retrieve a List<Product> from the context, and then i need to serialize the data and send it over the wire. If I use the exact Linq to Entities class, then there is so much "garbage" that it either fails outright (from a circular reference) or you are wasting bandwith. What is a design to overcome this? Create POCO objects for each linq to entities class you want to send and create custom code to tranform each object in the list to the POCO object? This is the only way I can see doing it, although it would be very tedius to create and maintain (unless some custom code generation was created). Is there a better approach? clients

Converting DateTime to SmallDateTime when OUTER JOIN

  
Hi, Here is the situation: I have a query with the following format:       Select a.*, b.* from (select col1, col2 from tbl1 t1 left join tbl2 t2 on t1.x = t2.x where t1.colSmallDateTime= MAX(t.ColDateTime)) a                     LEFT OUTER JOIN (select col1, col2 from tbl1 t1 left join tbl2 t2 on t1.x = t2.x where t1.colSmallDateTime= MIN(t.ColDateTime)) b                     ON (a.y = b.y) When i tun this query i get the following error:            Msg 298, Level 16, State 1, Line 1            The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. Does anyone have any idea why i am getting this error? Thank in advance for your help!  

SQL Server 2008 bug with outer join and index

  
Hello! We found a problem when migrating from SQL Server 2005 to SQL Server 2008. SQL-statements like   SELECT <table1>.<fields>, <table2>.<fields>, <table3>.<fields> FROM <table1> JOIN <table2> ON <table2>.<field> = <table1>.<field> LEFT JOIN <table3> ON <table3>.<field> = <table1>.<field> WHERE (<table2>.<field> = 2 AND <table2>.<another_field> = 'some_text' OR <table3>.<field> = 2 AND <table3>.<another_field> = 'some_text')   give different results on 2005 / 2008. On 2008 I get lots of exessive rows which have NULLs instead of the values required in the WHERE-term. Changing the Compability Level does nothing. It also doesn't matter from where the query is executed (direct via ODBC, in a view or from the Enterprise Manager). 2005/2008 also use different execution plans. (Btw: how can I make an screenshot of a EP larger than the screen?)   After some hours I drilled it down to the existence of a index! By deleting or deactivating the index 2008 gives the correct results like the 2005 did, after activating the index I get these exessive NULL-rows again!   Anyone else ran into this problem? Any tips (dropping all indexes in a 25+ GB DB is NOT an option :-) ?   Thanks in advance! Holger WellenkötterMCTS SQL 2005

Merge Join: Full Outer Join - keep key values in case of no-match

  
I'm using the Merge Join to join several different incoming flows into one flow.  I've configured the joins to use a Full Outer Join because I need all records from all sources. In the case of a no-match, I want the component to keep the values of join key fields instead of setting them to NULL.  How can I achieve that?  (Activating the checkbox doesn't help, because that adds a new field to the output instead of re-using the existing one.)

Trouble with Outer Join

  
Hi, I'm having an issue with an outer join due to my data structure. I have a view and table and I need to join them so that I get all the records from the table and the associated results from the view. The view structure is: Total | cID | cName | lID | lName 50       1       2 GB      1      Stock Room 100     2       4 GB      1      Stock Room The table structure is: cID | cName 1        2 GB 2        4 GB 3        8 GB 4       16 GB The result i'm looking for should therefore look like this returning all rows from the table, matching rows from the view and NULL for non-matching rows: cName | Total 2 GB       50 4 GB       100 8 GB       NULL 16 GB     NULL Probably something very basic i've missed. Thanks for the help in advance. MichaelWeb Developer MCTS SharePoint 2007 Developer

Get Distinct value from a Group-Join syntax Linq

  
Hello,I have this group-join syntax but I couldn't get the distinct value from the relational tables below.Table_1key_1 t1_value1     Food2     Sports3     Leisure 4     Trip5     ZooTable_2key_2   key_1  t2_boolean15        1          True16        1          True17        1          True18        2          True19        2          True20        2          Falsedesire result:FoodSports            Dim c = (From t1 In dbContext_Table_1 _                                     Group Join GroupTable In _db.Table_2 On GroupTable.key_1 Equals t1.key_1 Into Group 
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend