Using LINQ for multi-Table operations (2)

1: m or M: m Relational multi-Table operations

The table structure is as follows:

 

Write expression directly

// Normal selection
VaR user = context. Users. Where (P = & gt; p. userid = 10300). Select (P = & gt; new {P, P. usertags })
// Conditional Selection
VaR user = context. users. where (P => P. userid = 10300 ). select (P => New {P, usertags = P. usertags. where (O => O. tagid> 10 )})

Select [t0]. [userid], [t0]. [email], [t0]. [nickname], [T1]. [userid] as [userid2], [T1]. [tagid], (
Select count (*)
From [DBO]. [usertag] as [T2]
Where [T2]. [userid] = [t0]. [userid]
) As [value]
From [DBO]. [users] as [t0]
Left Outer Join [DBO]. [usertag] as [T1] on [T1]. [userid] = [t0]. [userid]
Where [t0]. [user id] = 10300
Order by [t0]. [userid], [T1]. [tagid]

Use loadoptionOption

VaR option = new dataloadoptions ();
Option. associatewith <user> (P => P. usertags. Where (O => O. tagid> 10 ));
Context. loadoptions = option;
VaR user = context. Users. Where (P => P. userid = 10300). Single ();
VaR usertags = user. usertags;

Here we need to change loadwith to associatewith, because the parameters following loadwith can only appear in the form of *. *. To put it bluntly, it can only be a whole record, and it cannot be used to filter any conditions.

Use joinStatement

The usage is the same as before. In addition, we can use contains () to achieve the same effect. The translated SQL statement will also change to exists or in.

Summary
In general, I still think join is the first choice if multi-Table operations are to be performed. As for loadoption, I can only sigh with emotion that "both generate join and he Sheng loadoption". Of course, if loadoption still has a secret, you can tell me what you want to know.
For the relationship between 1: m or M: M, no matter which method is used, a bunch of duplicate data will be selected. What if I don't want the duplicate data? For the data of the 1: M relationship, it is okay to take the data directly twice, but what about M: m? My current practice is to convert the parameter to a string after I take it for the first time, and then split it into SQL (using the CLR function). Do you know there is no better way?