you could put the contents of your csv list objects into a table value parameter. then call a stored procedure, passing in that tvp. the stored procedure could then run a cursor through the 300 databases and joins to your table value parameter (using ad-hoc sql). it will basically be a loop that iterates 300 times which isn't too bad. something like this:
create procedure yournewprocedure ( @tablevalueparameter dbo.udttvp readonly ) as declare @dbname varchar(255) declare @sql nvarchar(3000) declare db_cursor cursor local for select distinct name from sys.databases where name like '%yourdbs%' open db_cursor fetch next from db_cursor into @dbname while @@fetch_status = 0 begin set @sql = 'update t set t2.field = t.field from @tablevalueparameter t join [' + @dbname + ']..tableyoucareabout t2 on t.field = t2.field ' exec sp_executesql @sql, n'@tablevalueparameter dbo.udttvp', @tablevalueparamete fetch next from db_cursor into @dbname end close db_cursor deallocate db_cursor
you seem to have the basic idea right. hitting the database once for every line in the csv is going to be way too slow. you can create a "where in" statement via linq like so:
var addresses = getemailaddresses(); var entries = ctx.entries.where(e => addresses.contains(e.emailaddress));
however, if you have too many addresses in your list, it'll take a long, long time to generate and evaluate your query. i'd recommend splitting your input list up into batches of a reasonable size (200 entries?), and then using the trick above to handle each batch with a single database check.
once you've got that working, you can try a few other things to see if they make a measurable difference performance-wise:
- tweak the batch size.
- run the batches independently with varying degrees of parallelism.
- play with indexes on the database tables, particularly on the email address field.
- order the email addresses before breaking them into batches. it's possible that the db queries will take better advantage of hard disk caching strategies.
- Recommended programming pattern for multiple lookups
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet'
- Does this LINQ code perform multiple lookups on the original data?
- How to reuse a linq expression for 'Where' when using multiple source tables
- Avoid extra loop and could not find implementation of query pattern for source type int Select not found
- Could not find an implementation of the query pattern for source type
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet` 'Where' not found
- How to assign multiple LINQ Include() statements to a variable for code re-use?
- C# SQL/Linq/Entity Framework calculating column totals for multiple columns from large data source
- Getting "Could not find an implementation of the query pattern for source type 'ExcelQueryable<T>'. " Error
- source code for LINQ 101 samples
- Could not find an implementation of the query pattern for source type 'Join'
- Lambda expression for multiple parameters
- LINQ Single() Exception for 0 or multiple items
- Linq query with multiple Contains/Any for RavenDB
- Is there a .NET queue class that allows for dequeuing multiple items at once?
- How to pass multiple Expressions to OrderBy for EF?
- LINQ to SQL - How to efficiently do either an AND or an OR search for multiple criteria
- Dealing with Queries in a Repository Pattern with multiple concrete implementations?
- Common query for multiple similar entity types in Entity Framework
- LINQ to SQL: Complicated query with aggregate data for a report from multiple tables for an ordering system
- Suggestions for designing complex LINQ code
- LINQ Solution for Multiple Resolves
- Is there a suggested pattern for using LINQ between the Model & DataAccess Layers in a DDD based Layered Architecture
- RavenDB search for each of multiple terms using StartsWith
- The data reader has more than one field. Multiple fields are not valid for EDM primitive types
- ASP.NET Web API GET Method: Passing multiple values for single parameter
- LINQ Source Code Available
- multiple orderby in this linq code
- .NET: recommended video tutorial for LINQ?
More Query from same tag
- how to implement join using LINQ and EntityFramework
- Linq lambda expression to get at least one duplicate along with non duplicates
- Validate if URL exist in List using linq
- WCF Service operation always returns false
- Search by zip code and filter list within specific radius?
- Linq to SQL: execution order when calling SubmitChanges()
- How to create an asynchronous repository with EF Core?
- Struggling to understand Lambda expressions with LINQ
- ReSharper Search pattern for .First([With condition]) ignoring .First()
- SQL Select Where In converted to Linq
- How to find all the rows that match a list of combinations in Linq/SQL?
- Concat not working - The underlying array is null
- Combined actions in LINQ for IEnumerable/List?
- Is there a way to more easily combine Expressions and lambdas?
- Sum of two column from different table and it should return result if secondary table does not have any data
- Linq join query displayed in MVC view
- Simplifying a Linq search in an HTMLAgilitypack document
- DeSerialize From XML By LINQ
- vb.net LINQ select Distinct to a List
- Writing LINQ to XML query for filtering a xml string
- C# Linq where clause as a variable
- .ToList throwing exception
- Linq from TABLE select *
- Lambda Expressions and searching
- Transfor List to Nested Dictionary using linq C#
- Avoid LINQ with Let keyword doing self-loop
- Do Views degrade the LINQ query performance?
- Can't convert a list of objects with a child list if the child list is empty using linq?
- handling empty strings using linq
- why the sql query is different on that linq query when run on c# and on vb.net?