Come fare una subquery in LINQ?

Ecco un esempio della query che sto cercando di convertire in LINQ:

SELECT * FROM Users WHERE Users.lastname LIKE '%fra%' AND Users.Id IN ( SELECT UserId FROM CompanyRolesToUsers WHERE CompanyRoleId in (2,3,4) ) 

Esiste una relazione FK tra CompanyRolesToUsers e Users , ma è una relazione molti a molti e CompanyRolesToUsers è la tabella di giunzione.

Abbiamo già creato la maggior parte del nostro sito e abbiamo già la maggior parte del filtraggio funzionante costruendo espressioni utilizzando una class PredicateExtensions.

Il codice per i filtri semplici ha un aspetto simile al seguente:

  if (!string.IsNullOrEmpty(TextBoxLastName.Text)) { predicateAnd = predicateAnd.And(c => c.LastName.Contains( TextBoxLastName.Text.Trim())); } e.Result = context.Users.Where(predicateAnd); 

Sto cercando di aggiungere un predicato per una sottoselezione in un’altra tabella. ( CompanyRolesToUsers )

Quello che mi piacerebbe poter aggiungere è qualcosa che fa questo:

 int[] selectedRoles = GetSelectedRoles(); if( selectedRoles.Length > 0 ) { //somehow only select the userid from here ???: var subquery = from u in CompanyRolesToUsers where u.RoleID in selectedRoles select u.UserId; //somehow transform this into an Expression ???: var subExpression = Expression.Invoke(subquery); //and add it on to the existing expressions ???: predicateAnd = predicateAnd.And(subExpression); } 

C’è un modo per fare questo? È frustrante perché posso scrivere facilmente la stored procedure, ma sono nuovo di questa cosa LINQ e ho una scadenza. Non sono stato in grado di trovare un esempio che corrisponde, ma sono sicuro che è lì da qualche parte.

Ecco una subquery per te!

 List IdsToFind = new List() {2, 3, 4}; db.Users .Where(u => SqlMethods.Like(u.LastName, "%fra%")) .Where(u => db.CompanyRolesToUsers .Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId)) .Select(crtu => crtu.UserId) .Contains(u.Id) ) 

Riguardo questa parte della domanda:

 predicateAnd = predicateAnd.And(c => c.LastName.Contains( TextBoxLastName.Text.Trim())); 

Consiglio vivamente di estrarre la stringa dalla casella di testo prima di creare la query.

 string searchString = TextBoxLastName.Text.Trim(); predicateAnd = predicateAnd.And(c => c.LastName.Contains( searchString)); 

Vuoi mantenere un buon controllo su ciò che viene inviato al database. Nel codice originale, una ansible lettura è che una stringa non tagliata viene inviata al database per il ritaglio, il che non è un buon lavoro da fare per il database.

Non c’è bisogno di subquery con questa affermazione, che è meglio scritta come

 select u.* from Users u, CompanyRolesToUsers c where u.Id = c.UserId --join just specified here, perfectly fine and u.lastname like '%fra%' and c.CompanyRoleId in (2,3,4) 

o

 select u.* from Users u inner join CompanyRolesToUsers c on u.Id = c.UserId --explicit "join" statement, no diff from above, just preference where u.lastname like '%fra%' and c.CompanyRoleId in (2,3,4) 

Detto questo, in LINQ sarebbe

 from u in Users from c in CompanyRolesToUsers where u.Id == c.UserId && u.LastName.Contains("fra") && selectedRoles.Contains(c.CompanyRoleId) select u 

o

 from u in Users join c in CompanyRolesToUsers on u.Id equals c.UserId where u.LastName.Contains("fra") && selectedRoles.Contains(c.CompanyRoleId) select u 

Che, ancora una volta, sono entrambi modi rispettabili per rappresentarlo. Preferisco la syntax esplicita “join” in entrambi i casi, ma eccola …

Questo è il modo in cui ho fatto subquery in LINQ, penso che questo dovrebbe ottenere ciò che desideri. È ansible sostituire l’CompanyRoleId esplicito == 2 … con un’altra subquery per i diversi ruoli che si desidera o unirsi a essa.

 from u in Users join c in ( from crt in CompanyRolesToUsers where CompanyRoleId == 2 || CompanyRoleId == 3 || CompanyRoleId == 4) on u.UserId equals c.UserId where u.lastname.Contains("fra") select u; 

Potresti fare qualcosa del genere per il tuo caso – (la syntax potrebbe essere un po ‘spenta). Guarda anche questo link

 subQuery = (from crtu in CompanyRolesToUsers where crtu.RoleId==2 || crtu.RoleId==3 select crtu.UserId).ToArrayList(); finalQuery = from u in Users where u.LastName.Contains('fra') && subQuery.Contains(u.Id) select u; 

Ok, ecco una query di join di base che ottiene i record corretti:

  int[] selectedRolesArr = GetSelectedRoles(); if( selectedRolesArr != null && selectedRolesArr.Length > 0 ) { //this join version requires the use of distinct to prevent muliple records //being returned for users with more than one company role. IQueryable retVal = (from u in context.Users join c in context.CompanyRolesToUsers on u.Id equals c.UserId where u.LastName.Contains( "fra" ) && selectedRolesArr.Contains( c.CompanyRoleId ) select u).Distinct(); } 

Ma ecco il codice che più facilmente si integra con l’algoritmo che avevamo già in atto:

 int[] selectedRolesArr = GetSelectedRoles(); if ( useAnd ) { predicateAnd = predicateAnd.And( u => (from c in context.CompanyRolesToUsers where selectedRolesArr.Contains(c.CompanyRoleId) select c.UserId).Contains(u.Id)); } else { predicateOr = predicateOr.Or( u => (from c in context.CompanyRolesToUsers where selectedRolesArr.Contains(c.CompanyRoleId) select c.UserId).Contains(u.Id) ); } 

che è grazie a un poster sul forum LINQtoSQL

Ecco una versione di SQL che restituisce i record corretti:

 select distinct u.* from Users u, CompanyRolesToUsers c where u.Id = c.UserId --join just specified here, perfectly fine and u.firstname like '%amy%' and c.CompanyRoleId in (2,3,4) 

Inoltre, si noti che (2,3,4) è un elenco selezionato da un elenco di caselle di controllo da parte dell’utente della web app, e ho dimenticato di menzionare che ho semplicemente codificato quello per semplicità. In realtà si tratta di una serie di valori di CompanyRoleId, quindi potrebbe essere (1) o (2,5) o (1,2,3,4,6,7,99).

Inoltre l’altra cosa che dovrei specificare più chiaramente è che PredicateExtensions è usato per aggiungere dynamicmente clausole di predicato al Where for the query, a seconda dei campi del modulo che l’utente della web app ha compilato. Quindi la parte più difficile per me è come per trasformare la query di lavoro in un’espressione LINQ che posso albind all’elenco dinamico di espressioni.

Fornirò alcune delle query LINQ di esempio e vedrò se riesco ad integrarle con il nostro codice, e poi postare i miei risultati. Grazie!

marcel