Reusing Lambda Select Fragments in LINQ

c# entity-framework-core lambda linq

Question

I would like to be able to reuse fragments of my select lambda expressions in my Entity Framework Core 2.0 queries.

For example:

var result = await ctx.Customers
  .Select(cust => new CustomerDto {
    CustomerId = cust.Id,
    CustomerName = cust.Name,
    CurrentValue = cust.Orders
      .Where(order => order.OrderDate >= DateTime.Now.AddDays(-30)
      .Sum(order => order.TotalValue)
    })
    .ToListAsync();

Since I might want to calculate the CurrentValue property in other queries (in practice the sub-query is more complex than this), I would ideally like to refactor the above code to something like:

var result = await ctx.Customers
  .Select(cust => new CustomerDto {
    CustomerId = cust.Id,
    CustomerName = cust.Name,
    CurrentValue = CalculateCustomerCurrentValueExpr(cust)
  })
  .ToListAsync();

I have created Linq predicates using a Linq.Expression, but I have been unable to find a way to use an Expression as an element of the select statement.

Any help would be much appreciated.

Update - Performance with .AsExpandable()/.Invoke()

For anyone interested, I ran some test code ten times which produced the following result:

Standard Inline Code: 17ms (58,609 ticks) With .AsExpandable() and inline code 16ms (58,029 ticks) With .AsExpandable() and .Invoke() 16ms (58,224 ticks)

I suspect that if more test cycles had been run, the average processing time for all three scenarios would have been the same - at least with the level of accuracy I could measure at (simple StopWatch()).

Thanks to all contributors, particularly SergeyA for the solution and Ivan Stoev for the simple explanation of .AsExpandable()

1
3
2/2/2018 8:59:28 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

You can reuse expressions with AsExpandable extension from LinqKit liblary (http://www.albahari.com/nutshell/linqkit.aspx).

Example:

Expression<Func<Customer,long>> func = c => c.Orders
  .Where(order => order.OrderDate >= DateTime.Now.AddDays(-30)
  .Sum(order => order.TotalValue);

var result = await ctx.Customers
  .AsExpandable() // this allow to unwrap injected expression
  .Select(cust => new CustomerDto {
    CustomerId = cust.Id,
    CustomerName = cust.Name,
    CurrentValue = func.Invoke(cust) // this inject predefined expression
  })
  .ToListAsync();
4
2/1/2018 5:27:37 PM

Popular Answer

I store my expressions in a static file and reuse the expressions where I need them to ensure to include all related data. Maybe this can work for you as well

In GetStore() I reuse an expression called ClientAccess and passes that to the expression at ShopExpressions.

GetPage() uses a simple straight forward implementation.

ShopExpressions.cs:

public static IQueryable<IStore> StoreLite(IQueryable<IStore> dbSet)
{
    var result = dbSet
        .Include(str => str.VATs)
            .ThenInclude(vat => vat.VAT)
                .ThenInclude(vat => vat.Culture)
                    .ThenInclude(cult => cult.Items)
                        .ThenInclude(itm => itm.Culture)
        .Include(str => str.Options)
            .ThenInclude(opt => opt.Items)
                .ThenInclude(itm => itm.Option)
        .Include(str => str.Cultures)
            .ThenInclude(cult => cult.Items)
                .ThenInclude(itm => itm.Culture)
                    .ThenInclude(cult => cult.Items)
                        .ThenInclude(itm => itm.Culture)
        .Include(str => str.Pages)
            .ThenInclude(page => page.Sections)
                .ThenInclude(section => section.Elements);

    return result;
}

public static IQueryable<IStore> Store(IQueryable<IStore> dbSet)
{
    var result = StoreLite(dbSet)
        .Include(str => str.Categorys)
            .ThenInclude(cat => cat.Products)
                .ThenInclude(prd => prd.InfoItems)
                    .ThenInclude(itm => itm.Culture)
                        .ThenInclude(cult => cult.Items)
                            .ThenInclude(itm => itm.Culture);

    return result;
}

public static IQueryable<IPage> Page(IQueryable<IPage> dbSet)
{
    var result = dbSet
        .Include(page => page.Sections)
            .ThenInclude(sec => sec.Elements)
        .Include(page => page.CSS)
        .Include(page => page.Script)
        .Include(page => page.Meta);

    return result;
}

Controller.cs:

[HttpGet]
public async Task<IStore> GetStore(int id)
{
    IStore result = await ShopExpressions.Store(GenericExpressions.ClientAccess(this.Worker.GetRepo<Store>().DbSet))
        .SingleAsync(str => str.Id.Equals(id));

    this.Worker.ValidateClientAccess(result);

    return result;
}

[HttpGet]
public async Task<IStore> GetStoreLite(int id)
{
    IStore result = await ShopExpressions.StoreLite(GenericExpressions.ClientAccess(this.Worker.GetRepo<Store>().DbSet))
        .SingleAsync(str => str.Id.Equals(id));

    this.Worker.ValidateClientAccess(result);

    return result;
}

[HttpGet]
public async Task<IPage> GetPage(int id)
{
    IPage result = await ShopExpressions.Page(this.Worker.GetRepo<Page>().DbSet)
        .SingleAsync(page => page.Id.Equals(id));

    return result;
}
Stein The Ruler

Related Questions

How to select Distinct / Group By child property in both LINQ method and syntax?

I have class...public class Parent { [Key] public int ParentID { get; set;} public virtual ICollection<Child> Childs { get; set; } } public class Child { [Key] public int ChildID { get; set; } public int Grade { get; set; } public int ParentID { get; set; } [ForeignKey("ParentID")] public virtual Parent Parent ...
c# entity-framework entity-framework-6 linq
asked by warheat1990

Entity Framework Core - LINQ select over navigational properties creates invalid query

Currently, I'm working on an Asp.Net Core website and use Entity Framework Core (currently RC1) for database access with SQL Express LocalDB on the development machine, although MS SQL on the test server gives the same error....I've run into an issue that when querying multiple levels of navigational properties, invalid SQL would be generated. I cr...
c# entity-framework-core linq sql
asked by GeorgDangl

Value cannot be null Parameter name: source at System.Linq.Enumerable.Select[TSource,TResult]

I am getting this error for below code. ...var person = await context.Person.Where(x=>x.ID == 1).ToControllerModel().SignleOrDefaultAsync(); return person.ToViewModel(); static IQueryable<Person> ToControllerModel(this IQueryable<Person> query) { return query.Select(i => new PersionModel{ Name = i.Name, ...
c# ef-core-2.0 entity-framework entity-framework-core
asked by Bhavesh

How to create a Stuff and XML PATH(SQL) in Linq or Lambda

How to create the equivalent code in Linq or lambda?...I'm using Entity Framework Core on Asp.net Core 2....SELECT (SELECT STUFF((SELECT DISTINCT ', ' + Roles.Name FROM AspNetUsers Users INNER JOIN AspNetUserRoles UserRoles ON UserRoles.UserId = Users.Id INNER JOIN AspNetRoles Roles ON ...
c# entity-framework-core lambda linq sql-server
asked by Leonardo Pavanello

LINQ: Select the Min and Max values from a collection on an entity after grouping

The goal is to get the first DateTime and Last DateTime from a collection on an Entity (Foreign Key). My Entity is an organization and my collection are Invoices. I'm grouping results since Organizations unfortunately are not Unique. I'm dealing with duplicate data and cannot assume my organizations are unique so I'm grouping by a Number field on m...
.net-core c# entity-framework-core linq
asked by Orion

How to return same object with linq as with lambda

I'm trying to use object with Entity Framework Core when I use linq query. But object is always anonymous, which I don't want. I have this code: ... var query = (from c in context.UserTest join k in context.OneToTest on c.FkOneToTestId equals k.Id select new { c, k }).ToList(); ...whi...
c# entity-framework entity-framework-core linq
asked by marcel novák

How select hierarchy from child to parent using linq for entities or EF Core 2?

There is a hierarchy of four entities (models). All entities have relationships one to many. It's looks following:...public class Region { public Guid Id { get; set; } public int RegionId { get; set; } public virtual ICollection<District> Districts { get; set; } } public class District { public Guid I...
c# entity-framework-core linq parent-child
asked by Seva

Why Linq "where" expression after Select gets evaluated locally when created through a generic method?

I think the problem with your code is ...GetCriteria<MySimpleEntity>(); ...linq... cannot translate that directly to ...sql... or doesn't have a direct translation. If you want to use it. Execute ...ToList()... then add ....Where(someCriteria2).ToList();.... In watcher it sees/evaluate it as the same. But in the query itself, generating the sql doe...
c# entity-framework-core linq
asked by Gabriel Llorico

How can I reuse a subquery inside a select expression?

How about doing this like...await dbContext.Organizations .AsNoTracking() .Select(x => new OrganizationListItem { Id = x.Id, Name = x.Name, OwnerFirstName = x.Members.FirstOrDefault(member => member.Role == RoleType.Owner).FirstName, OwnerLastName = x.Members.FirstOrDefault(member => m...
c# entity-framework-core
asked by Hamza Khanzada

LINQ - Entity Framework Core - SELECT most recent record, only if it matches another condition

I have a dataset that looks like this. I want to select all rows where the most recent record for a node has a status of 'Pending'...Nodeid Status Type Utctimestamp ------ ----- ------ -------- 1 Pending Transaction 2020-03-13 03:31:00.00000+00 1 Overridden Transaction 2020-03-13 03:32:00.00000+00 2...
entity-framework-core linq-to-entities
asked by Chris Halcrow

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow