How do you reuse mapping functions on Nested entities in Entity Framework?

Question

I have seen multiple questions that are similar to this one but I think my case is slightly different. I'm using EF6 to query the database and I'm using data projection for better queries.

Given that performance is very important on this project I have to make sure to just read the actual fields that I will use so I have very similar queries that are different for just a few fields as I have done this I have noticed repetition of the code so I'm been thinking on how to reuse code this is currently what I Have:

 public static IEnumerable<FundWithReturns> GetSimpleFunds(this DbSet<Fund> funds, IEnumerable<int> fundsId)
    {
        IQueryable<Fund> query = GetFundsQuery(funds, fundsId);

        var results = query
            .Select(f => new FundWithReturns
            {

                Category = f.Category,
                ExpenseRatio = f.ExpenseRatio,
                FundId = f.FundId,
                Name = f.Name,
                LatestPrice = f.LatestPrice,
                DailyReturns = f.FundDailyReturns
                                    .Where(dr => dr.AdjustedValue != null)
                                    .OrderByDescending(dr => dr.CloseDate)
                                    .Select(dr => new DailyReturnPrice
                                    {
                                        CloseDate = dr.CloseDate,
                                        Value = dr.AdjustedValue.Value,
                                    }),
                Returns = f.Returns.Select(r => new ReturnValues
                     {
                         Daily = r.AdjDaily,
                         FiveYear = r.AdjFiveYear,
                         MTD = r.AdjMTD,
                         OneYear = r.AdjOneYear,
                         QTD = r.AdjQTD,
                         SixMonth = r.AdjSixMonth,
                         ThreeYear = r.AdjThreeYear,
                         YTD = r.AdjYTD
                     }).FirstOrDefault()
            })
            .ToList();
        foreach (var result in results)
        {
            result.DailyReturns = result.DailyReturns.ConvertClosingPricesToDailyReturns();
        }
        return results;
    }

 public static IEnumerable<FundListVm> GetFundListVm(this DbSet<Fund> funds, string type)
    {
        return funds
                .Where(f => f.StatusCode == MetisDataObjectStatusCodes.ACTIVE
                 && f.Type == type)
                 .Select(f => new FundListVm

                 {
                     Category = f.Category,
                     Name = f.Name,
                     Symbol = f.Symbol,
                     Yield = f.Yield,
                     ExpenseRatio = f.ExpenseRatio,
                     LatestDate = f.LatestDate,
                     Returns = f.Returns.Select(r => new ReturnValues
                     {
                         Daily = r.AdjDaily,
                         FiveYear = r.AdjFiveYear,
                         MTD = r.AdjMTD,
                         OneYear = r.AdjOneYear,
                         QTD = r.AdjQTD,
                         SixMonth = r.AdjSixMonth,
                         ThreeYear = r.AdjThreeYear,
                         YTD = r.AdjYTD
                     }).FirstOrDefault()
                 }).OrderBy(f=>f.Symbol).Take(30).ToList();
    }

I'm trying to reuse the part where I map the f.Returns so I tried created a Func<> like the following:

private static Func<Return, ReturnValues> MapToReturnValues = r => new ReturnValues
    {
        Daily = r.AdjDaily,
        FiveYear = r.AdjFiveYear,
        MTD = r.AdjMTD,
        OneYear = r.AdjOneYear,
        QTD = r.AdjQTD,
        SixMonth = r.AdjSixMonth,
        ThreeYear = r.AdjThreeYear,
        YTD = r.AdjYTD
    };

and then use like this:

public static IEnumerable<FundListVm> GetFundListVm(this DbSet<Fund> funds, string type)
    {
        return funds
                .Where(f => f.StatusCode == MetisDataObjectStatusCodes.ACTIVE
                 && f.Type == type)
                 .Select(f => new FundListVm

                 {
                     Category = f.Category,
                     Name = f.Name,
                     Symbol = f.Symbol,
                     Yield = f.Yield,
                     ExpenseRatio = f.ExpenseRatio,
                     LatestDate = f.LatestDate,
                     Returns = f.Returns.Select(MapToReturnValues).FirstOrDefault()
                 }).OrderBy(f=>f.Symbol).Take(30).ToList();
    }

The compiler is ok with it but at runtime, it crashes and says: Internal .NET Framework Data Provider error 1025

I tried to convert the Func into Expression like I read on some questions and then using compile() but It didn't work using AsEnumerable is also not an option because It will query all the fields first which is what I want to avoid.

Am I trying something not possible?

Thank you for your time.

Ivan Stoev · Accepted Answer · 2017-11-09 20:00:57Z

1

It definitely needs to be Expression<Func<...>>. But instead of using Compile() method (not supported), you can resolve the compile time error using the AsQueryable() method which is perfectly supported (in EF6, the trick doesn't work in current EF Core).

Given the modified definition

private static Expression<Func<Return, ReturnValues>> MapToReturnValues =
    r => new ReturnValues { ... };

the sample usage would be

Returns = f.Returns.AsQueryable().Select(MapToReturnValues).FirstOrDefault()

answered Nov 9, 2017 at 20:00

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Luis Palacios Over a year ago

Yes! that was it worked and it produced the exact same SQL output, so close yet so far... Thanks a lot

Luis Palacios Over a year ago

This is fun. I'm now using EF core and I'm wondering how I do it on Core?

Ivan Stoev Over a year ago

Too bad, because AsQueryable is still unsupported in EF Core. There are plans to support it in some future release AFAIK, but you have to wait. In order to reuse expressions currently you'll need some 3rd party expression helper library - for instance, LINQKit. But note that with or w/o expression reuse, EF Core query translation is still far from perfect and leads in many scenarios to client evaluation / N + 1 queries.

Collectives™ on Stack Overflow

How do you reuse mapping functions on Nested entities in Entity Framework?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related