3

Let's say I have a List of Detail class with 1000 entries. How can I exactly retrieve the matching data from the database Details table using LINQ method with a combination of both FirstCode and SecondCode properties?

public class Detail
{
  public string FirstCode  { get; set; }
  public string SecondCode { get; set; }
}

If we're going to retrieve a single data it would be like this:

foreach(var detail in details)
{
    var retrievedData = context.Details
        .Where(x => x.FirstCode == detail.FirstCode && x.SecondCode == detail.SecondCode)
        .FirstOrDefault();

    // Add to some list here
}

But I don't want to fetch 1000 times from the database, also I don't want to get all data from Details table and then do the searching within the .NET level, because it's not ideal if we have bunch of data(for ex. 500,000+ records in Details table).

Igor
  • 60,821
  • 10
  • 100
  • 175
xird
  • 775
  • 1
  • 8
  • 16
  • Your `Where()` clause will generate the proper SQL statement that would only return the relevant rows from the database. That's the purpose of Linq-to-Entities (which is part of EntityFramework). – haim770 Oct 10 '18 at 10:58
  • Join Your Details with details based on detail.Firstcode and detail.SecondCode and retrieve final result at once – Amin Mozhgani Oct 10 '18 at 11:01
  • I *think* what you are saying is that the `details` collection has `1000` or more items in it which causes that many trips to the database due to the `foreach` clause which you are trying to avoid? Where is `details` being populated? Is `details` a user fed list (input) *or* is this retrieved from the database immediately before the loop? Show more code. – Igor Oct 10 '18 at 11:01
  • Is it possible really? – isaeid Oct 10 '18 at 11:02
  • @Igor yes, is there a way for me to do a one time call, getting that 1000 records from the 500,000+ records in the database? – xird Oct 10 '18 at 11:02
  • @xird - Possibly but you have to answer my question first otherwise any offered solution is just a guess. – Igor Oct 10 '18 at 11:03
  • @Igor the details list is user input – xird Oct 10 '18 at 11:04
  • Are you using Sql Server as backend or something else (if so please specify the rdbms) – Igor Oct 10 '18 at 11:05
  • @Igor Yes, SQL server – xird Oct 10 '18 at 11:06
  • 1
    Why do you want to query your database for 2000 values? Can't you insert those values in a temp table, and select the data you want using a join? – CodeCaster Oct 10 '18 at 11:10
  • 1
    You have 2 options. You can use a [table valued parameter](https://stackoverflow.com/q/8157345/1260204) ([example2](https://stackoverflow.com/a/33773336/1260204)), this is more code but will be more performant if you are using indexes on these values because it would result in the use of an index seek. Or you can concatenate the values (first+second) in the lookup and create a string array and the use `Contains` and concatenate the values in the database table as the input of the contains call. This is easier to do but is less performant because it essentially results in a table scan. – Igor Oct 10 '18 at 11:10
  • 1
    I'm with CodeCaster here. Trying to write a query with 2000 filter values is massive, and strongly suggests that this is an [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). The proposed solution (Y) is not good, and you should instead ask about the X problem you seek to solve. – Flater Oct 10 '18 at 13:04
  • Are you EF core or EF6? – Filip Cordas Oct 10 '18 at 16:35

1 Answers1

2

You need to programmatically generate the 'where' clause. Start with a query that returns all the rows in the Details database table...

IQueryable<XDetail> queryable = (from d in context.Details select d);

...where XDetail is the class type of the database table. I assume it is different from the Detail class in your question. Now you need to generate all the clauses to the query that specify the list of entries we want...

var predicate = PredicateBuilder.False<XDetail>();
foreach(Detail d in details)
    predicate = predicate.Or((xd) => xd.FirstCode == d.FirstCode && 
                                     xd.SecondCode == d.SecondCode));

queryable = queryable.Where(predicate);
var results = queryable.ToList();

You can see the code for the PredicateBuilder class here. Note that Entity Framework will generate the required SQL but there is a limit to how big that query can be. So adding 1000 clauses will certainly make it to big. You would have to experiment but you might be limited to a 100 or less before you hit the limit.

Phil Wright
  • 22,580
  • 14
  • 83
  • 137