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 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.

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

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

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

回答1:

ANSWER

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

回答2:

The first example requires var because you are changing the shape of the query by projecting into anonymous type. The second example can either use var or IQueryable<VendorProfileViewModel> because you are changing the shape of the query by projecting into VendorProfileViewModel.