CODEWITHSUNDEEP
c#linqlinq-to-sql
SumGuy
SumGuy

Reputation: 622

Load only necessary data Linq to Sql and ExpandoObject

I am trying to reduce the sql server calls from the following (simplified) query:

var timequery = from t in TimeRecords
                select t;

var reducedResults = timequery.Select(delegate(TimeRecord t){

    ExpandoObject result = new ExpandoObject();
    IDictionary<string,object> record = (IDictionary<string,object>)result;

    record["DisplayName"] = t.User.DisplayName; //this loads ENTIRE user record

   //Include Dynamic columns
   foreach (var expenseTypeDescription in usedExpenseTypes) //the usedExpenseTypes are the types in the results 
   {    
       //also loading entire expense and expense type records
       record[expenseType] = t.Expenses.Where(e => e.ExpenseType.Description = expenseTypeDescription ).Sum(e => e.Amount);
   }

   return (dynamic)result;
}).ToList();

If it makes any difference I will do reducedResults.OrderBy(t => t.Copies).Skip(page).Take(pageSize), where copies is one of the expense types in the dynamic columns, later on in the code. So, I don't want to pull all the results into memory before paging.

When I look at the generated sql, instead of pulling just the user displayname, it's completely populating the User record to get just the display name. I tried to cast through an anonymous type to get just the displayname, but then I can't use the delegate(TimeRecord t). Not using the delegate would be fine, but I don't know how to do the dynamic columns from the expense types with timequery.Select(t => new { t.User.DisplayName, //dynamic columns });

To summarize, how can I not load the entire User record?

Edit: The Simplified types are

class TimeRecord
{
     User User;
     EntitySet<Expense> Expenses;
     //other stuff
}

class User
{
     string DisplayName;
     //other stuff
}

class Expense
{
    decimal Amount;
    ExpenseType ExpenseType;
    //other stuff
}

class ExpenseType
{
     string Description;
     //other stuff
}

Upvotes: 1

Views: 333

Answers (1)

Rick james
Rick james

Reputation: 854

If you dont need the entire user entity, create a type that contains just the fields you need and cast to it

public class UserInfo
{
    public string DisplayName { get; set; }
    //include anything else you may need.
}

var timequery = from t in TimeRecords
                select new UserInfo
                {
                 DisplayName = t.User.DisplayName
                 };

Upvotes: 1

Related Questions