CODEWITHSUNDEEP
c#linqgroup-byentity-framework-core-2.2
Adhikar Hariram
Adhikar Hariram

Reputation: 5

How to get LINQ GroupBy Query to run on server

I have a query against a database which is meant to return a list of entities, which have first been grouped by PortfolioId and Instrument ID as shown below.

List<Holding>holdings= _opsRepo.TableHoldings
                    .GroupBy(th =>new { th.PortfolioId, th.InstrumentId })
                    .Select(g => g.OrderByDescending(tr => tr.TradeDate).First())
                    .ToList();

The Holding entity has the following:

public class Holding
    {
        public int Id { get; set; }
        public DateTime TradeDate { get; set; }
        public int PortfolioId { get; set; }
        public int InstrumentId { get; set; }
        public decimal MarketValue { get; set; }
        public decimal UnitPrice { get; set; }
        public decimal Nominal { get; set; }
        public DateTime LastUpdatedTime { get; set; }

        public Portfolio Portfolio { get; set; }
        public Instrument Instrument { get; set; }
    }

The issue that I'm getting is that the query is being evaluated locally, and cannot be executed on the server. The exact message is:

The LINQ expression 'GroupBy(new <>f__AnonymousType5`2(PortfolioId = [h].PortfolioId, InstrumentId = [h].InstrumentId), [h])' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'."}

We have the application configured such that it will throw an error and fail if a query is evaluated locally. Is there a way to re-write this query such that it will group and select the entities as I've written it?

A temporary solution that we've found is to first .ToList() the data set, and then run the grouping and select commands, but that would be bringing back a large amount of data as the table grows.

Upvotes: 0

Views: 95

Answers (0)

Related Questions