Implement left out join using LINQ

Implement left out join using LINQ

Address: http://geekswithblogs.net/AzamSharp/archive/2008/04/07/121103.aspx

Suppose you have a tblroom and tbluserinfo. now, you need to select all the rooms regardless of whether the room has user information or not. this callfor a left join which will select everything from the left
Side (the room side) regardless of the join on the right side. Here is the example.

Assume that you have two tables, tblroom and tbluserinfo ). Now you need to retrieve information about all the rooms, regardless of whether the room is occupied or not. This requires left join (left Outer Join). The left Outer Join will retrieve all rows in the left table of left join, regardless of whether the right table has matching items. The following is an example:

VaR list = from R in DC. tblrooms
Join UI in DC. tbluserinfos
On R. Username equals UI. usernameinto userrooms

From ur in userrooms. defaultifempty ()

Select New
{
Firstname = (Ur. firstname = NULL )? "N/A": UR. firstname,
Lastname = (Ur. lastname = NULL )? "N/A": UR. lastname,
Roomname = R. Name
};

The anonymous type replaces the "null" firstname and lastname with "N/A" (not available ).

Use "N/A" (not allowed)
If the firstname and lastname values are "null.

 

Appendix: Implementing left join for multiple tables using LINQ is as follows:

From: http://hi.baidu.com/xuejianxiyang/item/0c9df0175c8dbdfaddeecae3

Target SQL statement (Multi-table left join query)

Target SQL statement (Multi-table left join query) Select ID, name, jname, cname from userinfo u left join job J on u. job = J. jid left join City C on u. city = C. CID

The following figure shows how to implement the left join of the three tables using the LINQ to SQL statement:

VaR list = (from u in DC. userinfos join J in dc.jobs on u. job equals J. jid into j_joinfrom X in j_join.defaultifempty () join C in DC. cities on u. city equals C. CID into c_joinfrom V in c_join.defaultifempty () Select New {id = u. ID, name = u. name, jname = x. jname, cname = v. cname,/* U1 = u, X1 = x, V1 = V * // do not use the object method because the object may be null. property will throw an exception }). tolist (); For (VAR I = 0; I <list. count (); I ++) {console. writeline (list [I]. name + '\ t' + list [I]. jname + '\ t' + list [I]. cname); // if the field is null, no exception is reported. // console. writeline (list [I]. u1.name + '\ t' + list [I]. x1.jname + '\ t' + list [I]. v1.cname + "\ r \ n"); // The X1 V1 object may be null, throwing an exception} console. readline ();

The following is an example of left join for the three tables written by myself using LINQ:

EMP (employee table), DEPT (Department table), and kqemp (personnel attendance information table)