Is there any difference between these 2 linq queries?

Question

Imagine a many to many relationship between Authors and Items.

There appear to be 2 broad approaches when querying for Authors of an Item (examples below).

Why might I choose one approach over the other?

Is there any difference in how the SQL is generated for the 2 queries below?

Approach One

Find the Item by ID, and then use the Authors property to list the Authors.

List<Authors> Authors = db.Items
    .Where(i => i.ID == CurrentItemID)
    .FirstOrDefault()
    .Authors.ToList();

Approach Two

Find all Authors with AuthoredItems containing the current Item's ID.

List<Authors> Authors = db.Authors
    .Where(i => i.AuthoredItems.Any(a => a.ID == CurrentItemID))
    .ToList();

Many to Many Relationship Like So:

public class Item
{
    ...
    ICollection<Author> Authors {get;set;}
}

public class Author
{
    ...
    ICollection<Item> AuthoredItems {get;set;}
}

UPDATE

As suggested I have attempted to print the generated sql for these queries.

Approach 1 gives me:

SELECT 
[Project1].[ID] AS [ID], [Project1].[Title] AS [Title], 
[Project1].[C1] AS [C1], [Project1].[ID1] AS [ID1], 
[Project1].[Name] AS [Name] 

FROM ( 
  SELECT [Extent1].[ID] AS [ID], 
    [Extent1].[Title] AS [Title], 
    [Join1].[ID] AS [ID1], 
    [Join1].[Name] AS [Name], 
    CASE WHEN ([Join1].[Author_ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[Items] AS [Extent1] 

  LEFT OUTER JOIN (
    SELECT [Extent2].[Author_ID] AS [Author_ID], 
    [Extent2].[Item_ID] AS [Item_ID], 
    [Extent3].[ID] AS [ID], [Extent3].[Name] AS [Name] 
    ROM [dbo].[AuthoredItems] AS [Extent2] 
    INNER JOIN [dbo].[Authors] AS [Extent3] ON [Extent3].[ID] = [Extent2].[Author_ID] 
  ) 
  AS [Join1] ON [Extent1].[ID] = [Join1].[Item_ID] 
  WHERE 1 = [Extent1].[ID] 
) 
AS [Project1] 
ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC

Approach 2 gives me:

SELECT [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name] 
FROM [dbo].[Authors] AS [Extent1] 
WHERE EXISTS (
  SELECT 1 AS [C1] FROM [dbo].[AuthoredItems] AS [Extent2] 
  WHERE ([Extent1].[ID] = [Extent2].[Author_ID]) AND (1 = [Extent2].[Item_ID]) 
)

The sql from the second approach is much easier on my brain. And there's less joins etc. Am I right that the second approach would be more efficient?

Good idea. How would I go about doing that? I'm quite new to the whole linq / .net / mvc thing — Martin Hansen Lennox, Jan 04 '14 at 19:58
Remove `ToList` call and then use debugger or simply call `ToString` to get the statement. [Get SQL query from LINQ to SQL?](http://stackoverflow.com/questions/18237312/get-sql-query-from-linq-to-sql) — MarcinJuraszek, Jan 04 '14 at 20:01
Let us know what was the result. I'm curious about that. You should probably post an answer with that when you're done. — MarcinJuraszek, Jan 04 '14 at 20:06
FirstOrDefault should provide a overload that takes a predicate (so you can ommit the where in your first approach), This should stop before qurying all entries if found (don't know that exactly) — quadroid, Jan 04 '14 at 21:10
@MarcinJuraszek I have the sql for Approach 2 (db.Authors...) but I can't figure out how to get it for Approach 1 (db.Items...). Calling ToString() gives me `System.Data.Entity.DynamicProxies.Item_xxxx` — Martin Hansen Lennox, Jan 05 '14 at 12:09

score 1 · Answer 1 · answered Jan 04 '14 at 19:53

1

The first query seems more "natural" and easier to understand, because you know the item ID, and once you find the item, you just select all the listed authors.

In the other query, you look in every author if he is an author of the item, and if he is, you select him.

I would go with the first one. I can't be sure about the speed difference in the two queries but the first one is surely more simple to understand.

answered Jan 04 '14 at 19:53

Mateo Velenik

804
1
10
22

Thanks Mateo. That was my feeling too. But I have seen enough examples of the other approach to wonder if there was any reason for preferring that. – Martin Hansen Lennox Jan 05 '14 at 12:19

Is there any difference between these 2 linq queries?

1 Answers1