score:0
since you already have a t-sql solution, this is a good case for using a raw sql query. there will certainly be cases where a sql query cannot be expressed in linq or the linq statement doesn't generate an optimized query.
score:0
you can make a select that search all the strings that exists on the table and put in a list. after you check what strings that not exists.
example:
var founditems = tabletocheck
.where(x => hugelist.contains(x.id))
.select(x => x.id)
.distinct()
.tolist();
var notfounditems = hugelist.where(c => !founditems.any(x => x == c)).tolist();
this way you do just one request to the db.
score:5
t-sql can be a good approach, but in your case you would have to create a temporary table and make a join. 30k is not too many records, so it will probably be easier to compare records on the application side. in that case, you could do this:
var idlist = tabletocheck.select(x => x.id).tolist();
var notfounditems = hugelist.where(item => idlist.all(id => id != item));
since your strings from the database are ids, you can do even better and use hashset and contains method, which has complexity of o(1):
var idset = tabletocheck.select(x => x.id).tohashset();
var notfounditems = hugelist.where(item => !idset.contains(item));
ultimately, the performance depends on the size of the data set in the db. if the db table is huge and you would have to fetch millions of ids, then the t-sql approach will be faster.
Source: stackoverflow.com
Related Query
- How to select values in list that are NOT IN a Table using EF Core?
- How to select int that are not in some other list ?
- How to retrieve a List of Lists of strings that are not null or whitespace from a DataGridView using Linq
- How to select values within a provided index range from a List using LINQ
- Which LINQ query to select rows from 1 table that are not in another table
- How do I use Linq to find the elements of a list that are not present in another list?
- linq - how do you do a query for items in one query source that are not in another one?
- In a C# List how to select all columns + several custom columns, so that in each row all columns are flat (not nested)?
- Using LINQ I have a list of lists, how do I select all objects that exist in every list?
- How to get items that are and are not in a list
- How can I select using LINQ for an entry that contains a LIST with more than one row?
- How to retrieve one table row and list of rows that are connected to the first row in Linq .NET
- Get items from input list that do not exist in the database using EF Core 2.1
- How to get the list elements that are not into another list C#
- How do I get a list of integers that exist in one collection, but not in another of a different type using LINQ?
- Not able to select values from right list using LINQ join
- LINQ retrieve values from a table that of which fields(of a certain column) are not equal of another table
- Using LINQ and EF, how to remove values from database where not in list of items
- How to select top result for a given ID then join that into another table EF Core LINQ
- how to insert two values manually into table by using code
- How to dynamically group a list and select values using linq query in C#?
- Compare two DataTables with several keys and select the rows that are not present in second table
- Select from table using model properties that is not null
- How to select items that do not show up in a second list with Linq
- How to compare 2 lists/dictionaries using LINQ (Find values in list1 that are greater than values in list2)
- How to find the items that are repeated only once in the list using LINQ
- Using LINQ in C#, How to retrieve list of events based on specific dates, for example all events that are planned for upcoming 45 days?
- How to return all ranges that are not included in any other range C# list
- How to select elements from a table that are present in a junction table with LINQ - Entity Framework
- Entity Framework 4: SQL to Linq: How to select strings that are not uppercase?
More Query from same tag
- New object validation - DataTable to List<RelGeral>
- Get all the direct Children of a specified Parent
- Need Help Grouping in C#
- How to find a match in a list of LxWxT where Positions of L and W can vary
- How to do a linq group by
- Is there a neat way of doing a ToList within a LINQ query using query syntax?
- linq check if a value exists in a list
- C# string to number, TryParse returns true when Parse fails
- How can I convert IQueryable expression object into LINQ expression?
- Translate this VB.NET LINQ statement to C#, having trouble
- fastest way to compare if two ms sql tables are the same
- c# only show numbers after decimal point with linq
- Dynamic linq OrderBy() failing after adding HasValue in the expression
- LINQ in NEST framework for ElasticSearch
- LINQ to SQL grouping error in default VS object visualizer
- Issue with LINQ with a variable where clause
- Passing Linq list to MVC web form
- Linq and Cross Products
- Getting Keys and Values from IEnumerable<Dictionary<string, object>>
- Is there a way to create DbSets in Linqpad?
- Do while loop not looping when condition is true
- DefaultIfEmpty in LINQ
- ISNULL() in LINQ to XML
- How to include() nested child entity in linq
- Longest list in SortedList of Lists
- Create hyperlinks in MVC Action Method
- Join 2 tables by approximate timestamp
- C# LinQ Group By Multiple Columns Find Differences in Data
- Dynamic LINQ aggregates on IQueryable as a single query
- How to marge child data created by using group by in LINQ?