C#, EF: Create Linq query that compares on two properties

Question

So I've got a conundrum that I can't think my way out of. Basically, I have a Book object with two properties, Title and ISBN. What I want to say is, from this list of Books, check the database for a Book that has a matching combination.

Naive code would look like so:

foreach( var book in BookList )
{ 
   var matchingBook = 
        context.BookSet.Where( n => n.ISBN.Equals(book.ISBN ) 
           && n.Title.Equals(book.Title)).FirstOrDefault();

   if( matchingBook )
   ....
}

Clearly, this is a sub-optimal solution, but I don't know how to get around this other than to iterate over the books, create a composite key (ISBN + "-" + Title) and then send that to the DB, but that's feels a little sloppy because indexes can't be used.

Any help is greatly appreciated.

You need to have the BookId in your BookList, then you can simply get your book from the context with *find(bookId)*. — Rob, Oct 21 '16 at 04:48
And if you want just optimize query the solution is context.BookSet.FirstOrDefault( n => n.ISBN == book.ISBN && n.Title == book.Title); — Alex Pashkin, Oct 21 '16 at 04:56

score 0 · Answer 1 · answered Oct 21 '16 at 04:55

0

Just use join to get the matching data

var matchingBook =from bkSet in context.BookSet join bkList in BookList on bkSet.Title equals bkList.Title

answered Oct 21 '16 at 04:55

Abinash

471
3
13

score 0 · Answer 2 · answered Oct 21 '16 at 09:34

Since your requirement is equality check, solution is rather simple, you can create a keywords list to check and query like,

var keywords = bookList.Select(x => x.ISBN + x.Name);
var filteredBooks = context.Books.Where(x => keywords.Contains(x.ISBN + x.Name));
//other filters on filteredBooks if any goes here
var result= filteredBooks.ToList();

this will create an in clause in sql which will look something similar to,

SELECT 
    [Extent1].[ISBN] AS [ISBN], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Books] AS [Extent1]
    WHERE ([Extent1].[ISBN] + [Extent1].[Name] IN 
         (N'123-45Test 2', N'55656-78Test 3', 
          N'345345-67Test 4', N'5656-767Test 5')) 
    AND ([Extent1].[ISBN] + [Extent1].[Name] IS NOT NULL)

Ok, seems like you already considered this idea, sorry I overlooked that part of your question, but this is the most practical if you go through linq approach. Give it a go and see if execution plan used is any good — Mat J, Oct 21 '16 at 09:40

score 0 · Answer 3 · answered Oct 21 '16 at 11:29

I don't believe this is possible without resorting to a table valued function that takes the input titles and ISBNs as a table valued parameter. You might consider going with the concatenation approach as you suggested. If you need an index to get acceptable performance, you can add the composite key as a persisted computed column and index on that.

C#, EF: Create Linq query that compares on two properties

3 Answers3