c# - 在 Entity Framework 中跨多个表进行选择，从而产生通用的 IQueryable？

Question

我有一种情况，我创建了一个方法，该方法接受需求(规范)列表。

这些规范是在我的 PriceSpecification 对象中指定的，它有一个可以为 null 的属性列表，可以按要求工作(各种枚举或 ID)。

我使用 Entity Framework，我的规范可以是来自三个不同表的属性。

我想使用 LINQ 创建一个临时对象，如代码所示。然后根据规范中指定的属性，我想找到正确的对象并使用工厂模式制作 C# 对象。

当我使用单个表时，通常我会执行以下操作:

public List<Customer> GetCustomerBySpecification(CustomerSpecification specification)
    {
        IQueryable<DbCustomers> dbCustomers = repository.DbCustomers;

        if (specification.Id > 0)
        {
            dbCustomers = dbCustomers.Where(c => c.Id == specification.Id);
        }
        if (!string.IsNullOrEmpty(specification.Email))
        {
            dbCustomers = dbCustomers.Where(c => c.Email == specification.Email);
        }
        if (!string.IsNullOrEmpty(specification.ResetPasswordKey))
        {
            dbCustomers = dbCustomers.Where(c => c.ResetPasswordKey == specification.ResetPasswordKey);
        }

        return customerFactory.Create(dbCustomers.OrderBy(c=>c.Id).Skip(specification.Skip).Take(specification.Take).ToList());
    }

但是，现在我使用三个表。我试图制作您可以在下面看到的代码。问题是 where 语句给我错误:Cannot apply operator '==' to operands of ype 'int' and 'LetterColor', candidates are: [...] 。

从 test_obj.Where() 中，我只需要能够从连接在一起的三个表中获取 ID 属性。

 public Price GetPriceBySpecification(PriceSpecification specification)
        {
            var test_obj = from d in repository.DbPricing
                join d1 in repository.DbOfficeProducts on d.OfficeProductId equals d1.Id
                join d2 in repository.DbOfficeProductDetails on d1.ProductDetailsId equals d2.Id
                select new
                {
                    PricingId = d.Id,
                    LetterColor = d2.LetterColor,
                    LetterPaperWeight = d2.LetterPaperWeight
                };


            if (specification.LetterColor.HasValue)
            {
                test_obj = test_obj.Where(c => c.LetterColor == specification.LetterColor.Value);
            }
            if (specification.LetterPaperWeight.HasValue)
            {
                test_obj = test_obj.Where(c => c.LetterPaperWeight == specification.LetterPaperWeight.Value);
            }

            // Convert found data into a C# object using a Factory class

            // Return awesome stuff

            throw new NotImplementedException();
        }

所以我的问题是:

如何修改我的 GetPriceBySpecification(PriceSpecification specification) 方法，以便我可以使用具有所有三个表的属性的 where 语句，然后获取 ID？ :)

编辑:

添加了更多代码:

public class PriceSpecification:Specifications
    {
        public LetterColor? LetterColor { get; set; }
        public LetterPaperWeight? LetterPaperWeight { get; set; }
        public LetterProcessing? LetterProcessing { get; set; }
        public LetterSize? LetterSize { get; set; }
        public LetterType? LetterType { get; set; }
    }

这些类型是这样的类型:

public enum LetterColor
{
    BlackWhite=0,
    Color=1
}

Answer 1

 public Price GetPriceBySpecification(PriceSpecification specification)
    {
        var test_obj = from d in repository.DbPricing
            join d1 in repository.DbOfficeProducts on d.OfficeProductId equals d1.Id
            join d2 in repository.DbOfficeProductDetails on d1.ProductDetailsId equals d2.Id
            //select new <- anonymous object 
            select new Price
            {
                PricingId = d.Id,
                LetterColor = d2.LetterColor,
                LetterPaperWeight = d2.LetterPaperWeight
            };


        if (specification.LetterColor.HasValue)
        {
            test_obj = test_obj.Where(c => c.LetterColor == specification.LetterColor.Value);
        }
        if (specification.LetterPaperWeight.HasValue)
        {
            test_obj = test_obj.Where(c => c.LetterPaperWeight == specification.LetterPaperWeight.Value);
        }

        // Convert found data into a C# object using a Factory class

        // Return awesome stuff

        throw new NotImplementedException();
    }