实体框架 - 选择特定的列 [英] Entity Framework - Selecting specific columns

问题描述

我有一个链接一个地点和排序依据子句中的两个表查询成功，但我想添加到只选择特定的列，而不是让一切回来。

I have a successful query that links two tables with a where and orderby clause, but I wanted to add to just select specific columns instead of getting everything back.

PART 1
当我尝试这我得到的排序依据行语法错误，如果我删除的排序依据行语法错误移动到哪里行。

PART 1 When I attempt this I get syntax errors on the orderby line, if I remove the orderby line the syntax errors move to the where line.

错误3无法隐式转换类型'System.Linq.IOrderedQueryable'到'System.Linq.IQueryable。一个显式转换存在（是否缺少强制转换？）

Error 3 Cannot implicitly convert type 'System.Linq.IOrderedQueryable' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?)

            IQueryable<VendorProfile> query = _db.VendorProfiles
            .Include("VendorCategories")
            .Include("VendorsSelected")
            .Select(s => new  { s.ProfileID, s.Name, s.CompanyName, s.City, s.State, s.DateCreated, s.VendorsSelected, s.VendorCategories })
            .Where(x => x.VendorsSelected.Select(s => s.UserName).Contains(HttpContext.Current.User.Identity.Name))
            .OrderBy(x => x.DateCreated);

       if (criteria.name != string.Empty)
            query = query.Where(v => v.Name.Contains(criteria.name));
        if (criteria.company != string.Empty)
            query = query.Where(v => v.CompanyName.Contains(criteria.company));
        if (criteria.startDate != null && criteria.endDate != null)
            query = query.Where(v => v.DateCreated > criteria.startDate && v.DateCreated < criteria.endDate);
        if (criteria.categories != null && !criteria.categoryMatchAll)
            query = query.Where(v => criteria.categories.AsQueryable().Any(cat => v.VendorCategories.Select(vendCat => vendCat.CategoryID).Contains(cat)));
        if (criteria.categories != null && criteria.categoryMatchAll)
            query = query.Where(v => criteria.categories.AsQueryable().All(cat => v.VendorCategories.Select(vendCat => vendCat.CategoryID).Contains(cat)));
        if (criteria.minorityType != null)
            query = query.Where(v => v.MinotiryOwned == criteria.minorityType);
        if (criteria.diversityClass != null)
            query = query.Where(v => v.DiversityClassification == criteria.diversityClass);

        return query.ToList();

第2部
我也想知道如果我能提取选中的列到一个视图模型类，所以我厌倦了这一点，我就排序依据线得到与上述相同的结果。

PART 2 I also wanted to know if I could extract the selected columns into a view model class, so I tired this and I get same results as above on the orderby line

错误4无法隐式转换类型'System.Linq.IOrderedQueryable'到'System.Linq.IQueryable。一个显式转换存在（是否缺少强制转换？）

Error 4 Cannot implicitly convert type 'System.Linq.IOrderedQueryable' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?)

推荐答案

答案

我想你帮我偶然发现的事实，类型不匹配。使得IQueryable的类型和选择新的类型和返回类型相同，使语法高兴。使用VAR不喜欢。

I think you helped me stumble upon the fact that the types don't match. Making the IQueryable type and the select new type and the return type the SAME makes the syntax happy. Using var does not like.

public IEnumerable<BrowseVendorModel> SearchVendors(CustomSearchModel criteria)
{
    IQueryable<BrowseVendorModel> query = _db.VendorProfiles
        .Include("VendorCategories")
        .Include("VendorsSelected")
        .Select(s => new BrowseVendorModel
        {
            ProfileID = s.ProfileID,
            Name = s.Name,
            CompanyName = s.CompanyName,
            City = s.City,
            State = s.State,
            DateCreated = s.DateCreated,
            VendorsSelected = s.VendorsSelected,
            VendorCategories = s.VendorCategories
        })
        .Where(x => x.VendorsSelected.Select(s => s.UserName).Contains(HttpContext.Current.User.Identity.Name))
        .OrderBy(x => x.DateCreated);

    if (criteria.name != string.Empty)
        query = query.Where(v => v.Name.Contains(criteria.name));
    if (criteria.company != string.Empty)
        query = query.Where(v => v.CompanyName.Contains(criteria.company));
    if (criteria.startDate != null && criteria.endDate != null)
        query = query.Where(v => v.DateCreated > criteria.startDate && v.DateCreated < criteria.endDate);
    if (criteria.categories != null && !criteria.categoryMatchAll)
        query = query.Where(v => criteria.categories.AsQueryable().Any(cat => v.VendorCategories.Select(vendCat => vendCat.CategoryID).Contains(cat)));
    if (criteria.categories != null && criteria.categoryMatchAll)
        query = query.Where(v => criteria.categories.AsQueryable().All(cat => v.VendorCategories.Select(vendCat => vendCat.CategoryID).Contains(cat)));
    if (criteria.minorityType != null)
        query = query.Where(v => v.MinotiryOwned == criteria.minorityType);
    if (criteria.diversityClass != null)
        query = query.Where(v => v.DiversityClassification == criteria.diversityClass);

    return query;
}

这篇关于实体框架 - 选择特定的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！