CODEWITHSUNDEEP
c#linq
user3428422
user3428422

Reputation: 4560

LINQ to SQL with a large database table increase performance

I am doing a search on a database table using a wildcard(The contains extension) Here is the code 

// This gets a list of the primary key IDs in a table that has 5000+ plus records in it
List<int> ids = context.Where(m => m.name.ToLower().Contains(searchTerm.ToLower())).Select(m => m.Id).ToList();


// Loop through all the ids and get the ones that match in a different table (So basically the FKs..)
foreach (int idin nameId)
{
    total.AddRange(context2.Where(x => x.NameID == id).Select(m => m.Id).ToList());
}

In there anything I could change in the LINQ that would result in getting the IDs faster?

Thanks

Upvotes: 1

Views: 539

Answers (2)

Zhivko Kabaivanov
Zhivko Kabaivanov

Reputation: 373

In terms of performance you can see tests that show if you search 1 string in 1 000 000 entries its around 100 ms.

Here is the link with tests and implementation.

for (int y = 0; y < sf.Length; y++)
    {
        c[y] += ss.Where(o => o.Contains(sf[y])).Count();
    }

Upvotes: 0

Yogu
Yogu

Reputation: 9445

I have not tested it, but you could do something along these lines:

var total =
    from obj2 in context2
    join obj1 in context1 on obj2.NameID equals obj1.Id
    where obj1.name.ToLower().Contains(searchTerm.ToLower())
    select obj2.Id

It joins the two tables, performing a cartesian product first and then limiting it to the pairs where the NameIds match (see this tutorial on the join clause). The where line does the actual filtering.

It should be faster because the whole matching is done in the database, and only the correct ids are returned.

If you had a Name property in the context2 item class that holds a reference to the context1 item, you could write it more readable:

var total = context2
    .Where(x => x.Name.name.ToLower().Contains(searchTerm.toLower()))
    .Select(x => x.ID);

In this case, Linq to SQL would do the join automatically for you.

Upvotes: 1

Related Questions