Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to do a subquery in LINQ?

Here's an example of the query I'm trying to convert to LINQ:

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

There is a FK relationship between CompanyRolesToUsers and Users, but it's a many to many relationship and CompanyRolesToUsers is the junction table.

We already have most of our site built, and we already have most of the filtering working by building Expressions using a PredicateExtensions class.

The code for the straightforward filters looks something like this:

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

I'm trying to add a predicate for a subselect in another table. (CompanyRolesToUsers)

What I'd like to be able to add is something that does this:

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); } 

Is there any way to do this? It's frustrating because I can write the stored procedure easily, but I'm new to this LINQ thing and I have a deadline. I haven't been able to find an example that matches up, but I'm sure it's there somewhere.

like image 216
marcel_g Avatar asked Jan 06 '09 23:01

marcel_g


People also ask

What is the syntax for a subquery?

You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc. A subquery is a query within another query. The outer query is known as the main query, and the inner query is known as a subquery.

How do you use select many?

SelectMany(<selector>) method For example, SelectMany() can turn a two-dimensional array into a single sequence of values, as shown in this example: int[][] arrays = { new[] {1, 2, 3}, new[] {4}, new[] {5, 6, 7, 8}, new[] {12, 14} }; // Will return { 1, 2, 3, 4, 5, 6, 7, 8, 12, 14 } IEnumerable<int> result = arrays.

How do I use subqueries in SQL?

First, in the Select clause, I'll use a subquery to add a new item to the select list. This query adds the total sales in Canada to the information about each Customer in Canada: Second, I use subqueries in my Join clause, to create a table I can run a query against.

Does LINQ support subqueries?

Technically speaking LINQ doesn't support subqueries (at least, not yet). There exist, however, lots of options for achieving the same results. Peter Vogel is a system architect and principal in PH&V Information Services.

How to realize a LINQ query?

You can realize a query by calling some method on it (ToList is a good example) or by working with individual objects in the result (by using a foreach loop to process each object in the query's result). This means that, like SQL, you can assemble complex LINQ queries out of simpler queries.

What is the use of into keyword in LINQ?

You can also use the into keyword to enable the result of a join or group clause to serve as the source for additional query clauses in the same query expression. In LINQ, a query variable is any variable that stores a query instead of the results of a query.


1 Answers

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.

like image 80
Amy B Avatar answered Sep 24 '22 01:09

Amy B