SQL到C＃实体框架

Question

有人可以跳入并帮助我完成代码吗？我想将此SQL查询转换为C＃EF。我已经在尝试下附上了。如果我有转介代码零件的示例，我应该能够完成其余部分。

如果有可能的转换，请启发我。任何帮助都将受到赞赏。谢谢！

SQL查询

SELECT cd.id, 
            cd.RunOrder,
            cd.ItemId,
            code =  
                case
                    when cd.IsItemAssembly=0 or cd.IsItemAssembly is null then
                        (select cast(ma.Code as varchar) as code
                        from materials.Material ma

                        inner join materials.MaterialPrice mp
                        on ma.ID=mp.MaterialID
                        and mp.HistoryStatusID=2

                        where cd.ItemID=ma.ID
                        )
                    else
                        (select cast(ass.Code as varchar) as code
                        from Materials.tblAssemblies ass

                        where cd.ItemID=ass.Assembly_ID
                        and ass.HistoryStatusID=2
                        )
                end,
            isnull(cd.IsItemAssembly,0) as IsItemAssembly,
            cd.ReferredItemID,
            ReferredCode =
                case
                    when cd.IsReferredItemAssembly=0 or cd.IsReferredItemAssembly is null then
                        (select cast(ma.Code as varchar) as code
                        from materials.Material ma

                        inner join materials.MaterialPrice mp
                        on ma.ID=mp.MaterialID
                        and mp.HistoryStatusID=2

                        where cd.ReferredItemID=ma.ID
                        )
                    else
                        (select cast(ass.Code as varchar) as code
                        from Materials.tblAssemblies ass

                        where cd.ReferredItemID=ass.Assembly_ID
                        and ass.HistoryStatusID=2
                        )
                end,
            isnull(cd.IsReferredItemAssembly,0) as IsReferredItemAssembly,
            cd.Factor,
            cd.LinkedCalculatorID,
            cast(lc.Name as varchar) as LinkedCalcName,
            cd.CalculatorRuleID,
            cast(cr.Name as varchar) as RuleName,
            cast(cr.Code as varchar) as RuleCode

        from dbo.Calculator ch
        inner join dbo.CalculatorDetail cd
        on ch.ID = cd.CalculatorID
            and ch.IsActive = 1

        inner join dbo.CalculatorRule cr
        on cd.CalculatorRuleID=cr.ID

        left join dbo.Calculator lc
        on  lc.ID = cd.LinkedCalculatorID
            and lc.IsActive = 1


        where ch.ID=@CalculatorID

        order by cd.RunOrder

我的尝试：

 var query = (from ch in dbContext.Calculators
                         join cd in dbContext.CalculatorDetails on ch.ID equals cd.CalculatorID where ch.IsActive == true
                         join cr in dbContext.CalculatorRules on cd.CalculatorRuleID equals cr.ID
                         join lc in dbContext.Calculators on cd.LinkedCalculatorID equals lc.ID where lc.IsActive == true
                         where ch.ID == calculatorIDParameter
                         orderby cd.RunOrder
                         select new GetCalculatorMaterialsModel
                         {
                             id = cd.ID,
                             RunOrder = cd.RunOrder,
                             ItemId = cd.ItemID,
                             
                             ReferredItemID = cd.ReferredItemID,
                             ReferredCode = 

                             IsReferredItemAssembly = cd.IsReferredItemAssembly == null ? false : true,
                             Factor = cd.Factor,
                             LinkedCalculatorID = cd.LinkedCalculatorID,
                             LinkedCalcName = lc.Name,
                             CalculatorRuleID = cd.CalculatorRuleID,
                             RuleName = cr.Name,
                             RuleCode = cr.Code
                         }).ToList();

Answer 1

首先，表之间的所有关系都应通过设置导航属性和配置其关联的FKS来涵盖。这避免了需要在EF LINQ语句中指定加入条件。

public class Calculator
{
    [Key]
    public int Id { get; set; }
    // ...

    [ForeignKey("CalculatorDetailsId")]
    public virtual CalculatorDetails CalculatorDetails { get; set; }
}

public class CalculatorDetails
{
    [Key]
    public int Id { get; set; }
    // ...

    [ForeignKey("LinkedCalculatorId")]
    public virtual Calculator LinkedCalculator { get; set; }
    [ForeignKey("CalculatorRulesId")]
    public virtual Calculator CalculatorRules { get; set; }

    public int ItemId { get; set; }
    public int ReferredItemId { get; set; }
}

这些绝不是完整的，但应该让您了解导航属性的外观。我不建议为FKS添加属性，而是建议Shadow属性链接FK。这使模型具有一个真理的来源。

它变得丑陋的是您基于推荐的材料或材料或组件之间的关联。这是非常糟糕的关系数据库设计，因为您无法依靠FK约束来强制执行参考完整性。这也意味着我们也不能在此处使用导航属性。

我可能会简单地保持初始查询，然后单独获取适当的推荐项目：

var data = dbContext.Calculators
    .Where(x => x.Id == calculatorIdParameter)
    .Select(x => new GetCalculatorMaterialsModel
    {
        id = x.ID, // Is this supposed to be the calculator ID or the CalculatorDetails ID?
        RunOrder = x.CalculatorDetails.RunOrder,
        ItemId = x.CalculatorDetails.ItemID,
                             
        ReferredItemID = x.CalculatorDetails.ReferredItemID,
        IsItemAssembly = x.CalculatorDetails.IsItemAssembly ?? false,
        IsReferredItemAssembly = x.CalculatorDetails.IsReferredItemAssembly ?? false,
        Factor = x.CalculatorDetails.Factor,
        LinkedCalculatorID = x.CalculatorDetails.LinkedCalculator.ID,
        LinkedCalcName = x.CalculatorDetails.LinkedCalculator.Name,
        CalculatorRuleID = x.CalculatorDetails.CalculatorRule.ID,
        RuleName = x.CalculatorDetails.CalculatorRule.Name,
        RuleCode = x.CalculatorDetails.CalculatorRule.Code
    }).Single();

然后根据需要的任何表查找并填充您的项目/推荐详细信息。从您的SQL中不清楚为什么您需要加入材料price，因为您似乎想要的何时需要材料。编码，或者为什么您会检查ISARTIVE，因为我认为ID是唯一的。如果他们指着不活动的材料/组件，您是否希望代码为#Null？

if (data.IsItemAssembly)
    data.ItemCode = _dbContext.Assemblies
        .Where(x => x.Id == data.ItemId && x.IsActive)
        .Select(x => x.Code)
        .SingleOrDefault();
else
    data.ItemCode = _dbContext.Materials
        .Where(x => x.Id == data.ItemId && x.IsActive)
        .Select(x => x.Code)
        .SingleOrDefault();

然后再次设置相关项目代码。

当然，可以找到一种将其归为一个Linq表达式的方法，但是可以说，它可以更难理解，在加载单个详细的项目中，这将非常有效。

总的来说，最好修复关系模型以更好地处理这种关系。 EF旨在使用有效的关系模型，并且可以很容易地进行性能。一旦您开始引入良好的DB设计偏差，事情就开始变得更加丑陋。