How can I optimise this LINQ query to only execute a single SQL command?

Question

I am using Linq-To-Sql to populate my business layer. Here is a snippet of a query I am working on:

fund.FundEntities = fundGroup.tFunds
    .Select(fe =>
    {
        var fundEntity = new FundEntity()
        {
            BankAccount = null,
            CloseDate = fe.closeDate ?? new DateTime(),
            Commitment = fe.commitment ?? 0,
            CommitmentEndDate = fe.closeDate ?? new DateTime(),
            Fund = fund
        };
        fundEntity.CapitalCalls = fe.tCapitalCalls
            .Select(cc =>
            {
                return new CapitalCall()
                {
                    Amount = cc.agrAmount ?? 0,
                    FundEntity = fundEntity
                };
            }
             );
        return fundEntity;
    });

When I run this code, it executes queries for individual CapitalCalls at runtime. Is there anyway I could re-architect this to keep the same business object structure (IE- the relations from Fund -> FundEntity -> CapitalCall within the business objects), but load full tables at a time? Ideally, there would be a single SQL query with lots of joins that would result in a fully populated Fund.

Answer 1

A solution to the problem would be to execute multiple queries at the same time and bind their resulting data together. While it is possible for LINQ to SQL to do this, it does not support such functionality natively.

PLINQO is an open source replace and enhance alternative to LINQ to SQL that adds additional functionality to the framework. Particularly, you would be interested in Batch Queries and Future Queries.

Check it out at http://www.plinqo.com

Hope that helps! -Tom DuPont (Member of PLINQO development the team)

Answer 2

var fund = GetFund();
var fundGroup = GetFundGroup();
var dataContest = GetDataContext();


List<int> feIds = fundGroup.tFunds.Select(fe => fe.FundEntityId).ToList();

 //before: iterate through local collection fundGroup.tFunds
 //  and issue one CapitalCall query per fundEntity.
 //after: send all the fundEntityIds into the database in one query
 //  that also fetches related CapitalCalls.
var query =
  from fe in dataContext.tFunds
  where feIds.Contains(fe.FundEntityId))
  let capitalCalls = fe.tCapitalCalls
  select new {FundEntity = fe, CapitalCalls = capitalCalls.ToList() };

foreach(var record in query)
{
  FundEntity fundEntity = new FundEntity()
  {
    CloseDate = record.fe.closeDate ?? new DateTime(),
    ...
  }
  fundEntity.CapitalCalls = ...
}

Answer 3

Seems to me like you're going backwards instead of forwards. You are trying to go Fund -> FundEntity -> CapitalCalls, but then trying to give each a reference to its container.

You could consider doing a simpler query for the database, calling ToList on it and then re-projecting that into your desired structure using Linq to Objects.

You could also consider writing the query the other way and using group by. Conceptually, you would get the capital calls, and group them by fund entity, and then group those by fund.

If you think you can live without the container references, the query you have can be expressed as below, which should use a join in one query.

fund.FundEntities = fundGroup.tFunds
.Select(fe =>
{
    new FundEntity
    {
        BankAccount = null,
        CloseDate = fe.closeDate ?? new DateTime(),
        Commitment = fe.commitment ?? 0,
        CommitmentEndDate = fe.closeDate ?? new DateTime(),
        CapitalCalls = fe.tCapitalCalls
        .Select(cc =>
        {
            new CapitalCall
            {
                Amount = cc.agrAmount ?? 0
            };
        }
    }
});

Answer 4

I would go with something like SteadyEddi (I have FundEntity included), but I think you can express it exactly like you want as this (or the equivalent non-LINQ way as SteadyEddi):

fund.FundEntities =
from fe in fundGroup.tFunds
select new FundEntity()
{
    BackAccount = null,
    CloseDate = fe.closeDate ?? new DateTime(),
    Commitment = fe.commitment ?? 0,
    CommitmentEndDate = fe.closeDate ?? new DateTime(),
    Fund = fund,
    CapitalCalls = from cc in fe.tCapitalCalls
                   select new CapitalCall()
                   {
                       Amount = cc.agrAmount ?? 0,
                       FundEntity = fundEntity
                   }
}

If not, I think something is going on in your database mapping which really should be re-factored anyway :) Bad names etc. CloseDate == CommitmentEndDate. The problem with your code may be that you're constructing the CapitalCalls yourself so LINQ-TO-SQL may not be able to decode the expression tree properly. But again, I obviously haven't tested my code so I don't whether my code even works.