How to do a subquery in LINQ?

by

Here’s a subquery for you!

List<int> IdsToFind = new List<int>() {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)
)

Regarding this portion of the question:

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

I strongly recommend extracting the string from the textbox before authoring the query. 

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

You want to maintain good control over what gets sent to the database. In the original code, one possible reading is that an untrimmed string gets sent into the database for trimming – which is not good work for the database to be doing.

More Related Contents:

  1. Returning IEnumerable vs. IQueryable
  2. Difference Between Select and SelectMany
  3. Return anonymous type results?
  4. How can I conditionally apply a Linq operator?
  5. LINQ to SQL – Left Outer Join with multiple join conditions
  6. LINQ to SQL and a running total on ordered results
  7. Understanding .AsEnumerable() in LINQ to SQL
  8. TransactionScope vs Transaction in LINQ to SQL
  9. How to return anonymous type from c# method that uses LINQ to SQL [duplicate]
  10. How to write Asynchronous LINQ query?
  11. Convert Linq Query Result to Dictionary
  12. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  13. How do you perform a CROSS JOIN with LINQ to SQL?
  14. LINQ Where Ignore Accentuation and Case
  15. Operation could destabilize the runtime?
  16. How to select only the records with the highest date in LINQ
  17. Linq query with nullable sum
  18. Custom Method in LINQ to SQL query
  19. LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  20. LINQ Expression to return Property value?
  21. Max or Default?
  22. Will using LINQ to SQL help prevent SQL injection
  23. How do I do a left outer join with Dynamic Linq?
  24. Get sum of two columns in one LINQ query
  25. Last and LastOrDefault not supported
  26. Linq “Could not translate expression… into SQL and could not treat it as a local expression.”
  27. Accessing System Databases/Tables using LINQ to SQL?
  28. Is Injection Possible through Dynamic LINQ?
  29. If Else in LINQ
  30. C# Linq to SQL: How to express “CONVERT([…] AS INT)”?

Leave a Comment