简化 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 个额外的模型参数，对于每个参数，都会创建一个新的 var _result 和 _SentSurveys..我只是想简化此代码。

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);
    }
}