score:1
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.
score:0
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
Source: stackoverflow.com
Related Articles
- 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?