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.
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.
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.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With