CODEWITHSUNDEEP
sql-serverperformanceentity-frameworklinq-to-entitiesprojection
Emmit
Emmit

Reputation: 411

Select a projection in Entity Framework and filter by it

Simplified example: I have a table with FirstName, LastName. I am interested in retrieving all the people whose full salutation is not longer by N, sorted by the length. To achieve that, I have code like this:

  var result = await Context.People
                .Select(p => new PersonWithSalutation
                {
                    FirstName = p.FirstName,
                    LastName = p.FirstName,
                    FullSalutation = p.FirstName + " " + p.LastName
                })
                .Where(p => p.FullSalutation.Length < maxLength)
                .OrderBy(p => p.FullSalutation)
                .Take(maxResults)
                .ToListAsync();

The query looks like this:

SELECT TOP (10) 
    [Project1].[C1] AS [C1], 
    [Project1].[Name] AS [Name], 
    [Project1].[Id] AS [Id], 
    [Project1].[C2] AS [C2]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        1 AS [C1], 
        ...calculated stuff... AS [C2]
        FROM [dbo].[People] AS [Extent1]
        WHERE ...exactly the same stuff... <= @p__linq__3
    )  AS [Project1]
    ORDER BY [Project1].[C2] ASC

This does the trick and generates a single query to the database. The problem is the calculated projection since it appears twice in the resulting query: once in the SELECT and then in an WHERE clause. This example is simplified, but in my real case I am doing heavy mathematical operations that I would prefer to be calculated only once. As you can see above, the C2 is reused in the order clause. I would like to do the same with the WHERE clause, which I assume would involve yet another subquery). How would I achieve this?

Upvotes: 0

Views: 771

Answers (1)

Slava Utesinov
Slava Utesinov

Reputation: 13488

Because query's building is very unpredictable process, you can spend a lot of time by looking for desired linq. That is why, I offer another approach. Add FullSalutation property to your POCO class and it will be calculated(Computed) by means of DB when FirstName or LastName is changed. At this case calculations will be performed when it is really necessary, without repeats, as you desired.

public class POCO
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string FullSalutation { get; private set; }
}

Then add new migration:

public override void Up()
{
    //AddColumn("dbo.People", "FullSalutation", x => x.String());
    Sql("ALTER TABLE dbo.People ADD FullSalutation AS FirstName + ' ' + LastName"); 
}
public override void Down()
{
    DropColumn("dbo.People", "FullSalutation");
}

Finally your query will look like this:

var result = await Context.People
             .Where(p => p.FullSalutation.Length < maxLength)
             .Select(p => new PersonWithSalutation
             {
                  FirstName = p.FirstName,
                  LastName = p.LastName,
                  FullSalutation = p.FullSalutation
             }).OrderBy(p => p.FullSalutation).Take(maxResults).ToListAsync();

Upvotes: 1

Related Questions