简化linq查询

Question

我已经尝试使用左外连接使用LINQ。每当我更改我的报告参数时，它都会给出相同的结果。

var _result = (from ls in SessionHandler.CurrentContext.LennoxSurveyResponses 
          from ml 
          in SessionHandler.CurrentContext.MailingListEntries 
           .Where(mle => mle.SurveyCode == ls.SurveyCode).DefaultIfEmpty() 
          from lists 
          in SessionHandler.CurrentContext.MailingLists 
           .Where(m => m.MailingListId == ml.MailingListId).DefaultIfEmpty() 
          from channel 
          in SessionHandler.CurrentContext.Channels 
           .Where(ch => ch.ChannelId == lists.ChannelId).DefaultIfEmpty() 
          from tmChannelGroup 
          in SessionHandler.CurrentContext.ChannelGroups 
           .Where(tcg => tcg.ChannelGroupId == channel.ChannelGroupId).DefaultIfEmpty() 
          from dmChannelGroup 
          in SessionHandler.CurrentContext.ChannelGroups 
           .Where(dcg => dcg.ChannelGroupId == tmChannelGroup.ParentChannelGroupId).DefaultIfEmpty() 
          from amChannelGroup 
          in SessionHandler.CurrentContext.ChannelGroups 
           .Where(acg => acg.ChannelGroupId == dmChannelGroup.ParentChannelGroupId).DefaultIfEmpty() 
          where (model.ChannelId != 0 && channel.ChannelId == model.ChannelId || 
           model.TMId != 0 && channel.ChannelGroupId == model.TMId || 
           model.DistrictId != 0 && dmChannelGroup.ChannelGroupId == model.DistrictId || 
           model.AreaId != 0 && amChannelGroup.ChannelGroupId == model.AreaId || 
           model.AreaId == 0 && amChannelGroup.ChannelGroupId == model.LoggedChannelGroupId || 
           model.DistrictId == 0 && dmChannelGroup.ChannelGroupId == model.LoggedChannelGroupId || 
           model.TMId == 0 && tmChannelGroup.ChannelGroupId == model.LoggedChannelGroupId || 
           model.ChannelId == 0 && tmChannelGroup.ChannelGroupId == model.LoggedChannelGroupId) 
          && (ml.EmailDate != null || ml.LetterDate != null || ml.EmailBounce == null) 
          select ls).ToList(); 

我有这个基于模型值重复（排序）的LINQ查询。我怎么能够缩短这个查询..如果我可以只使用一个var对象，而不是使用一堆为不同的参数..当你看到这个代码重复。

if (model.ChannelId != 0) 
{ 
    var _result = 
     (from ls in SessionHandler.CurrentContext.LennoxSurveyResponses 
     join ml in SessionHandler.CurrentContext.MailingListEntries on ls.SurveyCode equals ml.SurveyCode 
     join m in SessionHandler.CurrentContext.MailingLists on ml.MailingListId equals m.MailingListId 
     join ch in SessionHandler.CurrentContext.Channels on m.ChannelId equals ch.ChannelId 
     where ch.ChannelId == model.ChannelId 
     && ml.EmailBounce == null || ml.EmailBounce.Equals(false) 
     select ls).ToList(); 
    var _SentSurveys = 
     (from ml in SessionHandler.CurrentContext.MailingListEntries 
     join m in SessionHandler.CurrentContext.MailingLists on ml.MailingListId equals m.MailingListId 
     join ch in SessionHandler.CurrentContext.Channels on m.ChannelId equals ch.ChannelId 
     where ch.ChannelId == model.ChannelId 
     && (ml.EmailDate != null || ml.LetterDate != null || ml.EmailBounce == null) 
     select ml).ToList(); 
    model.SentSurveys = _SentSurveys.Count() > 0 ? _SentSurveys.Count() : 0; 
    model.CompletedSurveys = _result.Count() > 0 ? _result.Count() : 0; 
    model.PercentageComplete = model.SentSurveys != 0 ? model.CompletedSurveys/model.SentSurveys : 0; 
    //model.Referring = _result.Average(m => Convert.ToInt32(m.Question1Answer)); 
    model.Referring = Math.Round(_result.Select(m => string.IsNullOrEmpty(m.Question1Answer) ? 0 : Double.Parse(m.Question1Answer)).Average()); 
    model.ServicePerformance = Math.Round(_result.Select(m => string.IsNullOrEmpty(m.Question2Answer) ? 0 : Double.Parse(m.Question2Answer)).Average()); 
    model.InstallPerformance = Math.Round(_result.Select(m => string.IsNullOrEmpty(m.Question3Answer) ? 0 : Double.Parse(m.Question3Answer)).Average()); 
    model.ReferringLennox = Math.Round(_result.Select(m => string.IsNullOrEmpty(m.Question4Answer) ? 0 : Double.Parse(m.Question4Answer)).Average()); 
} 
else if (model.TMId != 0) 
{ 
    var _result = 
     (from ls in SessionHandler.CurrentContext.LennoxSurveyResponses 
     join ml in SessionHandler.CurrentContext.MailingListEntries on ls.SurveyCode equals ml.SurveyCode 
     join m in SessionHandler.CurrentContext.MailingLists on ml.MailingListId equals m.MailingListId 
     join ch in SessionHandler.CurrentContext.Channels on m.ChannelId equals ch.ChannelId 
     where ch.ChannelGroupId == model.TMId 
     select ls).ToList(); 
    var _SentSurveys = 
     (from ml in SessionHandler.CurrentContext.MailingListEntries 
     join m in SessionHandler.CurrentContext.MailingLists on ml.MailingListId equals m.MailingListId 
     join ch in SessionHandler.CurrentContext.Channels on m.ChannelId equals ch.ChannelId 
     where ch.ChannelGroupId == model.TMId 
     && (ml.EmailDate != null || ml.LetterDate != null || ml.EmailBounce == null) 
     select ml).ToList(); 
    model.SentSurveys = _SentSurveys.Count() > 0 ? _SentSurveys.Count() : 0; 
    model.CompletedSurveys = _result.Count() > 0 ? _result.Count() : 0; 
    model.PercentageComplete = model.SentSurveys != 0 ? model.CompletedSurveys/model.SentSurveys : 0; 

    model.Referring = _result.Select(m => string.IsNullOrEmpty(m.Question1Answer) ? 0 : Double.Parse(m.Question1Answer)).Average(); 
    model.ServicePerformance = _result.Select(m => string.IsNullOrEmpty(m.Question2Answer) ? 0 : Double.Parse(m.Question2Answer)).Average(); 
    model.InstallPerformance = _result.Select(m => string.IsNullOrEmpty(m.Question3Answer) ? 0 : Double.Parse(m.Question3Answer)).Average(); 
    model.ReferringLennox = _result.Select(m => string.IsNullOrEmpty(m.Question4Answer) ? 0 : Double.Parse(m.Question4Answer)).Average(); 
} 

，并有5个额外的模型参数和每个参数，一个新的变种_result和_SentSurveys是created..i只是想简化此代码。

Answer 1

我认为这首先重构可以使写这些查询更容易将是有益的。如果这不是一个选项，您可以使用一些CompiledQueries来减少重复查询。但是这样做并不能使它在效率方面“精简”，只是让你的代码更加清晰。此外，在这两种情况下，您的后期处理看起来几乎与大量不必要的检查相同。无需重复常见的东西。有一些重构，你可以做这样的事情：

// need to set up the compiled queries first 
static readonly Func<MyDataContextType, 
        MyModelType, 
        Func<MyDataContextType, MailingListEntry, MailingList, Channel, MyModelType, bool>, 
        IQueryable<LennoxSurveyResponse>> 
    GetResult = CompiledQuery.Compile(
     (MyDataContextType ctx, MyModelType mod, 
     Func<MyDataContextType, MailingListEntry, MailingList, Channel, MyModelType, bool> pred) => 
      from lsr in ctx.LennoxSurveyResponses 
      join mle in ctx.MailingListEntries on lsr.SurveyCode equals mle.SurveyCode 
      join ml in ctx.MailingLists on mle.MailingListId equals ml.MailingListId 
      join ch in ctx.Channels on ml.ChannelId equals ch.ChannelId 
      where pred(ctx, mod, mle, ml, ch) 
      select lsr); 

static readonly Func<MyDataContextType, MyModelType, IQueryable<MailingListEntry>> 
    GetSentSurveys = CompiledQuery.Compile(
     (MyDataContextType ctx, MyModelType mod) => 
      from mle in ctx.MailingListEntries 
      join ml in ctx.MailingLists on mle.MailingListId equals ml.MailingListId 
      join ch in ctx.Channels on ml.ChannelId equals ch.ChannelId 
      where ch.ChannelId == mod.ChannelId 
       && (mle.EmailDate != null || mle.LetterDate != null || mle.EmailBounce == null) 
      select mle); 

static readonly Func<MyDataContextType, MyModelType, MailingListEntry, MailingList, Channel, bool> 
    ChannelPredicate = CompiledQuery.Compile(
     (MyDataContextType ctx, MyModelType mod, 
     MailingListEntry mle, MailingList ml, Channel ch) => 
      ch.ChannelId == mod.ChannelId && ml.EmailBounce == null || !ml.EmailBounce.Value); 

static readonly Func<MyDataContextType, MyModelType, MailingListEntry, MailingList, Channel, bool> 
    TMPredicate = CompiledQuery.Compile(
     (MyDataContextType ctx, MyModelType mod, 
     MailingListEntry mle, MailingList ml, Channel ch) => 
      ch.ChannelGroupId == mod.TMId); 

static void UpdateModel(MyModelType model) 
{ 
    if (model.ChannelId == 0 && model.TMId == 0) return; 

    var currentContext = SessionHandler.CurrentContext; 
    var predicate = (model.ChannelId != 0) ? ChannelPredicate : TMPredicate; 
    var results = GetResults(currentContext, model, predicate).ToList(); 
    var sentSurveys = GetSentSurveys(currentContext, model).ToList(); 

    model.SentSurveys = sentSurveys.Count(); 
    model.CompletedSurveys = results.Count(); 
    model.PercentageComplete = model.SentSurveys != 0 ? model.CompletedSurveys/model.SentSurveys : 0; 

    model.Referring = _result.Average(m => string.IsNullOrEmpty(m.Question1Answer) ? 0 : Double.Parse(m.Question1Answer)); 
    model.ServicePerformance = _result.Average(m => string.IsNullOrEmpty(m.Question2Answer) ? 0 : Double.Parse(m.Question2Answer)); 
    model.InstallPerformance = _result.Average(m => string.IsNullOrEmpty(m.Question3Answer) ? 0 : Double.Parse(m.Question3Answer)); 
    model.ReferringLennox = _result.Average(m => string.IsNullOrEmpty(m.Question4Answer) ? 0 : Double.Parse(m.Question4Answer)); 

    if (model.ChannelId != 0) 
    { 
     // should be rounded 
     model.Referring = Math.Round(model.Referring); 
     model.ServicePerformance = Math.Round(model.ServicePerformance); 
     model.InstallPerformance = Math.Round(model.InstallPerformance); 
     model.ReferringLennox = Math.Round(model.ReferringLennox); 
    } 
}