c# - 基于查询 ASP.NET 从模型返回列表的一部分

Question

我有以下模型:

    public class Vendor
        {
            public int Id { get; set; }

            [Required]
            public string Name { get; set; }

            [Display(Name = "Date of Birth")]
            public DateTime? BirthDate { get; set; }

            [Required]
            [EmailAddress]
            public string Email { get; set; }

            [MinLength(10)]
            public string Telephone { get; set; }

            public string Notes { get; set; }

            public Currency Currency { get; set; }
            [Display(Name = "Currency")]
            public int? CurrencyId { get; set; }

            public List<Translation> Translations { get; set; }

            public byte? Rating { get; set; } 
        }

我正在实现一个 API，它将返回数据库中的所有供应商。问题是我需要在返回的 JSON 对象中包含满足某些规范的 Translations。现在，我的 API Controller 看起来像这样:

public IEnumerable<VendorDto> GetVendors(string sourceLanguage = null, string targetLanguage = null, string service = null)
        {
            var vendorsQuery = _context.Vendors
                .Include(v => v.Translations.Select(t => t.SourceLanguage))
                .Include(v => v.Translations.Select(t => t.TargetLanguage))
                .Include(v => v.Translations.Select(t => t.Service))
                .Include(v => v.Currency);

            var result = vendorsQuery.ToList();

            IEnumerable<Translation> filteredTranslationsResults;

            if (!String.IsNullOrWhiteSpace(sourceLanguage))
            {
                if (!String.IsNullOrWhiteSpace(targetLanguage))
                {
                    if (!String.IsNullOrWhiteSpace(service))
                    {
                        filteredTranslationsResults = vendorsQuery
                            .SelectMany(v => v.Translations)
                            .Where(t => t.SourceLanguage.Name == sourceLanguage)
                            .Where(t => t.TargetLanguage.Name == targetLanguage)
                            .Where(t => t.Service.Name == service);
                        vendorsQuery = vendorsQuery
                            .Where(v => v.Translations.Intersect(filteredTranslationsResults).Any());
                    }
                    else
                    {
                        filteredTranslationsResults = vendorsQuery
                            .SelectMany(v => v.Translations)
                            .Where(t => t.SourceLanguage.Name == sourceLanguage)
                            .Where(t => t.TargetLanguage.Name == targetLanguage);
                        vendorsQuery = vendorsQuery
                            .Where(v => v.Translations.Intersect(filteredTranslationsResults).Any());
                    }
                }
                else
                {
                    filteredTranslationsResults = vendorsQuery
                            .SelectMany(v => v.Translations)
                            .Where(t => t.SourceLanguage.Name == sourceLanguage);
                    vendorsQuery = vendorsQuery
                        .Where(v => v.Translations.Intersect(filteredTranslationsResults).Any());
                }
            }

            return vendorsQuery
                .ToList()
                .Select(Mapper.Map<Vendor, VendorDto>);
        }

所以基本上我需要返回所有具有源/目标语言和作为参数传递的服务的Vendors vendors 返回，我只需要那个 translations 和那个 source/target language 和 service。使用我当前的 Controller 实现，如果它找到一个 Vendor 在他的 Translations 列表中，我的 translation我正在寻找，它返回那个 Vendor 和他所有的 translations，因为 Intersect 只是说它是真的然后得到所有。我需要更改什么才能使返回的对象 (Vendor) 仅包含我要查找的 translation？

更新:翻译模型:

public class Translation
    {
        public int Id { get; set; }

        public Language SourceLanguage { get; set; }
        [Display(Name = "Source Language")]
        public int SourceLanguageId { get; set; }

        public Language TargetLanguage { get; set; }
        [Display(Name = "Target Language")]
        public int TargetLanguageId { get; set; }

        public Service Service { get; set; }
        [Display(Name = "Service")]
        public int ServiceId { get; set; }

        public int Price { get; set; }

        public UnitMeasure UnitMeasure { get; set; }
        [Display(Name = "Unit Measure")]
        public int UnitMeasureId { get; set; }
    }

Answer 1

嗯，这可能很糟糕，但根据您的模型，这是我想出的一件事:

但是请注意，根据您拥有的供应商和翻译的数量，在应用谓词之前调用 tolist 可能会快得多(例如，如果您的记录少于 100K，则将它们全部拉下来可能会更快)。

public IEnumerable<VendorDto> GetVendors(string sourceLanguage = null, string targetLanguage = null, string service = null)
        {
            var vendorsQuery = _context.Vendors
                .Include(v => v.Translations.Select(t => t.SourceLanguage))
                .Include(v => v.Translations.Select(t => t.TargetLanguage))
                .Include(v => v.Translations.Select(t => t.Service))
                .Include(v => v.Currency)
                // consider adding a .ToList() right here based on how many records you have


                Expression<Func<Translation, bool>> sourceLanguagePredicate = z => z.SourceLanguage.Name == sourceLanguage;
                Expression<Func<Translation, bool>> targetLanguagePredicate = z => z.TargetLanguage.Name == targetLanguage;
                Expression<Func<Translation, bool>> servicePredicate = z => z.Service.Name == service;

                var hasSource = !string.IsNullOrWhiteSpace(sourceLanguage);
                var hasTarget = !string.IsNullOrWhiteSpace(targetLanguage);
                var hasService = !string.IsNullOrWhiteSpace(service);
                if(hasSource)
                {
                    vendorsQuery = vendorsQuery.Where(x => x.Translations.AsQueryable().Any(sourceLanguagePredicate));
                }

                if(hasTarget)
                {
                    vendorsQuery = vendorsQuery.Where(x => x.Translations.AsQueryable().Any(targetLanguagePredicate));
                }

                if(hasService)
                {
                    vendorsQuery = vendorsQuery.Where(x => x.Translations.AsQueryable().Any(servicePredicate));
                }

                var vendors = vendorsQuery.ToList();

                foreach(var vendor in vendors)
                {
                    if(hasSource)
                    {           
                        vendor.Translations = vendor.Translations.Where(sourceLanguagePredicate).ToList();
                    }

                    if(hasTarget)
                    {           
                        vendor.Translations = vendor.Translations.Where(targetLanguagePredicate).ToList();
                    }

                    if(hasService)
                    {           
                        vendor.Translations = vendor.Translations.Where(servicePredicate).ToList();
                    }
                }

                return vendors.Select(Mapper.Map<Vendor, VendorDto>);
        }