CODEWITHSUNDEEP
c#linq
CutHimSomeSlack
CutHimSomeSlack

Reputation: 193

Passing a collection of item to search for in LINQ

I have a tab delimited text file I am importing its rows into a database but not all of its records, inserting only the rows that do not already exist in the database. So I loop through the lines of this file, call my repository query something like this inside that for-each loop:

 while (streamreader.Peek() > 0)
 {
    string[] currentRowValues = streamreader.ReadLine().Split(delimiter);
    string lastName = currentRowValues[columnHeadertoLocationy["LName"]];
    string firstName = currentRowValues[columnHeadertoLocationy["FName"]];
    string middleName = currentRowValues[columnHeadertoLocationy["FName"]];
    if(!repo.IsProviderEligibleForImport(lastName, firstName, middleName)
    {
        // ok he does not already exist in DB, so go ahead and insert it, etc       
    }
// ....
}


    public bool IsProviderEligibleForImport(string lastName, string firstName, string midName)
    {
        var query = (from p in this.Context.Providers
            where
                p.FirstName == firstName &&
                p.LastName == lastName &&
                p.MidName== midName
            select p).FirstOrDefault();

        if (query == null)
            return false;
        return true;
    }

so something like:

foreach row in TextFile
  bool allowInsert = IsProviderEligibleForImport(pass params)

So the problem I see is that my test file has 108450 rows! So with this approach I will be hitting Database queries 108450 times which destroys the performance! I was wondering if there is some smarter way so I pass all of the records to search for in one pass and not one time for each row?

Upvotes: 1

Views: 95

Answers (2)

Richard Deeming
Richard Deeming

Reputation: 31198

Assuming you're using SQL 2008 or higher, a stored procedure with a Table Valued Parameter is probably going to be the best approach.

Depending on how complicated your "insert it, etc." code is, you could either do the insert within the stored procedure, or you could return the new records to the code for processing.

The table type:

CREATE TYPE dbo.udt_ProviderImport AS TABLE 
(
    -- TODO: Match the data-types and lengths to your table:
    LastName    nvarchar(100) NOT NULL,
    FirstName   nvarchar(100) NOT NULL,
    MidName     nvarchar(100) NOT NULL
);

The stored procedure:
Remove the INSERT INTO (...) block if you just want to return the new records to your code for further processing.

CREATE PROCEDURE dbo.usp_ImportProviderIfEligible
(
    @data    dbo.udt_ProviderImport READONLY
)
As
BEGIN
    INSERT INTO dbo.Providers
    (
        LastName,
        FirstName,
        MidName
    )
    SELECT
        LastName,
        FirstName,
        MidName
    FROM
        @data As D
    WHERE
        Not Exists
        (
            SELECT 1
            FROM dbo.Providers As P
            WHERE P.LastName = D.LastName
            And P.FirstName = D.FirstName
            And P.MidName = D.MidName
        )
    ;
END

The C# code:

var dt = new DataTable();
dt.Columns.Add("LastName", typeof(string));
dt.Columns.Add("FirstName", typeof(string));
dt.Columns.Add("MidName", typeof(string));

while (streamreader.Peek() > 0)
{
    string[] currentRowValues = streamreader.ReadLine().Split(delimiter);
    string lastName = currentRowValues[columnHeadertoLocationy["LName"]];
    string firstName = currentRowValues[columnHeadertoLocationy["FName"]];
    string middleName = currentRowValues[columnHeadertoLocationy["MName"]];
    dt.Rows.Add(lastName, firstName, middleName);
}

var data = new SqlParameter("data", SqlDbType.Structured);
data.TypeName = "dbo.udt_ProviderImport";
data.Value= dt;

// Use this if you're inserting directly from the stored procedure:
Context.Database.ExecuteSqlCommand("EXEC dbo.usp_ImportProviderIfEligible @data", data);

// If you're returning the new records for further processing,
// create a type to hold the returned values, and use:
// var results = Context.Database.SqlQuery<ResultType>("EXEC dbo.usp_ImportProviderIfEligible @data", data);

Upvotes: 2

DLeh
DLeh

Reputation: 24395

I think this would work. Create a class or something to store your text file entries into:

public class TextEntry
{
    public string FirstName {get; set;}
    public string LastName {get; set;}
    public string Middle {get; set;}
}

Then, I believe you can hit the database once with the 2nd line below. (With a large file, it might take a long time). Then you can filter based on the entries in memory. 

var textList = new List<TextEntry>();

//load all the entries from the text file

var providersInDB = Context.Providers.Where(p => textList.Any(t => p.FirstName == t.FirstName && p.LastName == t.LastName && p.MidName == t.Middle));

var textEntriesNotInDB = textList.Where(t => !providersInDB.Any(p => p.FirstName == t.FirstName && p.LastName == t.LastName && p.MidName == t.Middle));
//add those text entries

To expand on this, you could also edit the TextEntry class to override the Equals method that accepts another TextEntry or a Provider, then you could use Contains instead of Any I believe.

Upvotes: 1

Related Questions