Entity Framework generated sql restrict related records

Question

I have the following linq select query that loops round all the 'Places' linked to 'adrianMember' that start with B, I only want to show the PlaceName. I have a navigation association from Member to Place but not from Place to Member.

using (var fdb = new FALDbContext())
{
    var adrianMember = fdb.Members.Find(1);

    foreach (string s in adrianMember.Places.Where(p=>p.PlaceName.StartsWith("B")).Select(p => p.PlaceName))
    {
        Console.WriteLine("- " + s);
    }
}

I have experimented with various linq syntax too, for example not using Find...

var adrianMember = fdb.Members.Where(m => m.MemberId == 1).FirstOrDefault();

and providing two linq queries, one to retrieve the member and then later retrieve the related places (and hopefully making the EF do some lazy deferred loading) but that still results in very inefficient sql.

using (var fdb = new FALDbContext())
{
    //Need the FirstOrDefault otherwise we will return a collection (first or default will return the inner collection
    //could have multiple members with multiple places
    var members = fdb.Members.Where(m=>m.FirstName == "Bob");

    foreach (var member in members)
    {
        var places = member.Places.Where(p => p.PlaceName.StartsWith("B")).Select(p => p.PlaceName);

        foreach (var place in places)
        {
            Console.WriteLine(place);
        }
    }
}

The SQL output gets all rows and all columns

exec sp_executesql N'SELECT 
[Extent1].[PlaceId] AS [PlaceId], 
[Extent1].[PlaceName] AS [PlaceName], 
[Extent1].[PlaceLocation] AS [PlaceLocation], 
[Extent1].[Member_MemberId] AS [Member_MemberId]
FROM [dbo].[Places] AS [Extent1]
WHERE [Extent1].[Member_MemberId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

Is there a way to restrict the sql to something like

SELECT PlaceName FROM Places WHERE MemberId = 1 AND PlaceName like 'B%'

I have a couple of situations in my project where the above generated sql will make the query too slow (20,000 records per member over 20 columns). Is linq clever enough to make the change if I do have more records?

As an addition to this I have discovered that you can remove the virtual keyword to switch off Lazy Loading. Then, explicitly load related entries & apply a filter (effectively attaching the records). However, it would be nice for the above to work like that automatically. If anyone has any input then I'd like to hear it. — atreeon, Jul 07 '12 at 17:41

score 2 · Answer 1 · edited May 23 '17 at 10:09

2

Try this:

using (var fdb = new FALDbContext()) 
{     
    var members = fdb.Members.Where(m=>m.FirstName == "Bob");      
    foreach (var member in members)     
    {         
        fdb.Places.Where(p => p.PlaceName.StartsWith("B") && p.MemberId == member.Id).Select(p => p.PlaceName);
        foreach (var place in places)         
        {             
            Console.WriteLine(place);         
        }     
    } 
}

There is a similar question here

edited May 23 '17 at 10:09

Community

1
1

answered Jul 07 '12 at 19:14

Tommy Grovnes

4,126
2
25
40

This will only work if FirstOrDefault is specified. When FirstOrDefault is specified then the sql query sent to sql server is not restricted so unfortunately this still does not result in very efficient queries. – atreeon Jul 09 '12 at 11:36
My bad, see revised code section in my answer, instead of going member.Places I select from fdb.Places, this yields the sql you want – Tommy Grovnes Jul 09 '12 at 13:06
Hmmm, yes this does work if...(sorry Tommy)...I have a navigation association from Places to Member but I don't. So, "p.Member.Id == member.Id" won't compile. (thanks for your interest) – atreeon Jul 09 '12 at 15:59
Any more suggestions or ideas? – atreeon Jul 09 '12 at 16:43
Last try :), edited my example to not use navigation association. – Tommy Grovnes Jul 09 '12 at 21:31
Ah, no, there is no association of Member from Places to Member, only Member to Places. However, I'm not sure if its really a problem to add that association but I'd rather not make too many changes to my model just because of Entity Framework. I'll put some things I found out up sometime tomorrow if you want to have a look. I still don't really understand why there is a difference. I guess I'll have to read a couple of books on EF & it'll become clearer (one is due to drop through my letterbox tomorrow morning) – atreeon Jul 09 '12 at 22:40

score 0 · Accepted Answer · answered Aug 07 '12 at 00:05

After a month away from this problem I thought I'd take a second look...

Firstly, I'm not really sure what I wanted to achieve!!! However, I do seem to have found some interesting and more efficient sql queries for what I wanted.

My first option is to use explicit loading using the Entry & Collection methods. This creates a SQL query to retrieve each corresponding Place record and, importantly, projects the columns I want (just the description) and restricts the rows (those Places beginning with L). This does create many queries though if I have many associated places but I can do this on the fly if I don't know which Places I want to retrieve.

    using (var fdb = new FALDbContext())
    {
        foreach (var member in fdb.Members)
        {
            var places = fdb.Entry(member)
                            .Collection(m => m.Places)
                            .Query()
                            .Where(p => p.PlaceName.StartsWith("L"))
                            .Select(p => p.PlaceName);

            foreach (var place in places)
            {
                Console.WriteLine(place);
            }
        }
    }

The second option is to use lazy loading but specify a tightly controlled bit of LINQ to Entities.

This goes to the database once and retrieves just the members I want and projects and restricts both tables and all in one lovely efficient looking sql query!

    using (var fdb = new FALDbContext())
    {
        var myList = fdb.Members
                            .Where(m => m.GenderShareWithId > 0)
                            .Select(m => new { m.MemberId, m.DisplayName, Places = m.Places.Where(p => p.PlaceName.StartsWith("L")).Select(p => p.PlaceName) });

        foreach (var item in myList)
        {
            Console.WriteLine(item.DisplayName);
            foreach (var place in item.Places)
            {
                Console.WriteLine(" - " + place);
            }
        }
    }


SELECT 
    [Extent1].[MemberId] AS [MemberId], 
    [Extent1].[DisplayName] AS [DisplayName], 
    [Extent2].[PlaceName] AS [PlaceName]
FROM  [dbo].[Members] AS [Extent1]
LEFT OUTER JOIN [dbo].[Places] AS [Extent2] 
    ON ([Extent1].[MemberId] = [Extent2].[Member_MemberId]) AND ([Extent2].[PlaceName] LIKE N'L%')
WHERE [Extent1].[GenderShareWithId] > 0

Entity Framework generated sql restrict related records

2 Answers2