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.
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
- Recommended programming pattern for multiple lookups
- Does this LINQ code perform multiple lookups on the original data?
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet'
- Dealing with Queries in a Repository Pattern with multiple concrete implementations?
- How to reuse a linq expression for 'Where' when using multiple source tables
- LINQ Source Code Available
- multiple orderby in this linq code
- .NET 4 Code Contracts: "requires unproven: source != null"
- 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
- creating Linq to sqlite dbml from DbLinq source code
- Linq sub query when using a repository pattern with EF code first
- Code Cleanup: Best way to split long statement over multiple lines
- 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
- Accessing a List multiple times in same code block - Any better approach?
- How do i delete Multiple Row of Record in this code
- Cleaning up multiple IF statements in the code
- source code for LINQ 101 samples
- How to sort list on multiple properties in one line of code in Linq
- Could not find an implementation of the query pattern for source type 'Join'
- Code First CTP: Multiple PKs or FKs
- map one to one with multiple primary key columns entity framework code first
- Writing code that can work with multiple database objects
- List or Array of String Contain specific word in Html Source Code
- c# Linq or code to extract groups from a single list of source data
- Repository Pattern Access Multiple Tables?
- Search records with code pattern in column value c#
- C# group by to eliminate duplicates
- Pattern for Multi User ASP.NET with SQL/LINQ
- linq query with group
- Entity Framework 5 - Get non related / navigable entities
- Linq - How do I coalesce these into a single IQueryable?
- Linq to Entites Count Where Clause Syntax
- Listing Count per "Category" for IQueryable
- How to use Any between two ienumerables
- Replace specific Objects in IQueryable query in .NET fails
- how to use group by on complex objects?
- Validation on hierarchical alike structure
- how to do a contains on an array of strings?
- Get properties from second (Right) joined entity with Group by
- c# datatable groupby and sum column's values (without know the name)
- How I can search a field in sql
- Intermittent "Specified cast is invalid" with StructureMap injected data context
- Combining data using Linq
- A controller with this name is not registered in Angulaj Js with Sql Database
- Character interpretation
- How can I use a Lambda bool method within a WHERE clause?