C# - IQueryable query how to select?

Question

IQueryable<ImportNameValidation> query = entities.ImportNameValidation
    .Where(y => y.FirstName == searchedName)
    .Where(x => x.NameType == comboValue);

List<ImportNameValidation> ResultValues = query.ToList();  

In this query, I get back 6 columns but I only need 3 of them, how can I use the select method to get only those columns that I need? is it something like

.Select(t => t.FirstName, u => u.Name, i => i.NameCode);

what I really want in SQL is instead of "select *" I want to "select NameCode, Name, FirstName" but I need that as an IQueryable.

Solution 1

To select specific columns you need to project to an object with those properties (anonymous or custom)

.Select(t => new { t.FirstName, t.Name, t.NameCode })

In addition you can put the two where conditions in the same predicate:

entities.ImportNameValidation
        .Where(y => y.FirstName == searchedName && y.NameType == comboValue)
        .Select(t => new { t.FirstName, t.Name, t.NameCode })    

Or in query syntax:

from item in entities.ImportNameValidation
where item.FirstName == searchedName && item.NameType == comboValue   
select new { item.FirstName, item.Name, item.NameCode }

---

As the items in the collections are no longer of type ImportNameValidation you cannot assign this to a List<ImportNameValidation>. To do so project to a custom DTO object that contains there 3 properties (you cannot project to the mapped type - will cause an error):

List<ImportNameValidationDTO> result = entities.ImportNameValidation
    .Where(y => y.FirstName == searchedName && y.NameType == comboValue)
    .Select(t => new ImportNameValidationDTO { t.FirstName, t.Name, t.NameCode })
    .ToList();

Solution 2

Simple use Anonymous Types:

.Select(t => new { t.FirstName, t.Name, t.NameCode})

Solution 3

To cast it into the list of same object type first fetch the data as enumerable.

List<ImportNameValidation> ResultValues = query.AsEnumerable().Select(t => new ImportNameValidation { t.FirstName, t.Name, t.NameCode })  ;  

Comments

[Ibrahim Aweidah]

entities.ImportNameValidation.Where(y => y.FirstName == searchedName).Where(x => x.NameType == comboValue).Select(t => new { t.FirstName, t.Name,t.Remarks,t.NameType}); Im getting an error telling me im missing a cast see error : Severity Code Description Project File Line Suppression State Error CS0266 Cannot implicitly convert type 'System.Linq.IQueryable<<anonymous type: string FirstName, string Name, string Remarks, string NameType>>' to 'System.Linq.IQueryable<IImportNameValidation>'. An explicit conversion exists (are you missing a cast?)

[Travis J]

To note, this is no longer of type ImportNameValidation

[Travis J]

You don't need AsEnumerable here.

[Harsh]

@TravisJ Using the same domain type would cause issue. So, if you need to project the data in domain entity, you need as enumerable. Which is the case here.

[Travis J]

AsEnumerable will cause the entire query to be pulled into memory, and erase the gains of only selecting a subset of the fields.

[Harsh]

@TravisJ Yes, but not adding AsEnumerable will not allow the list to be generated because of the domain model. So, either use ViewModel or you need to pull the data first. Tell me if i am wrong.

[Travis J]

I don't understand why the domain model would prevent this query from completing. As an aside, I didn't downvote this, I think this should work (without the AsEnumerable). For example, see this here: dotnetfiddle.net/KdrqtJ . This should work as is.

[Harsh]

@TravisJ It's not about the down vote :) . Here is the problem i am referring to stackoverflow.com/questions/5325797/…

[Travis J]

Hm, I don't have my environment to test with, but I think that if you explicitly pass the type to Select, such as .Select<ImportNameValidation>(t => new ImportNameValidation() { ... } ) it could work.

[Ibrahim Aweidah]

Got this error : Severity Code Description Project File Line Suppression State Error CS0266 Cannot implicitly convert type 'System.Linq.IQueryable<<anonymous type: string FirstName, string Name, string NameCode>>' to 'System.Linq.IQueryable<CaseImport.ImportNameValidation>'. An explicit conversion exists (are you missing a cast?)

[Gilad Green]

@XxXIbbeXxX - Please refer to the last part of my answer to understand why. Also for that error there are many previous SO questions. The problem is that you are assigning the result from the query above to a collection of a different type

[Ibrahim Aweidah]

I tried even the last suggestion and got the following : Severity Code Description Project File Line Suppression State Error CS0246 The type or namespace name 'ImportNameValidationDTO' could not be found (are you missing a using directive or an assembly reference?)

[Gilad Green]

@XxXIbbeXxX - Please put some effort into solving your problem. ImportNameValidationDTO does not exist yet. You need to create it.

[Gilad Green]

@XxXIbbeXxX - Did you manage?

[Ibrahim Aweidah]

Nope, but had a workaround. dataGridView_Result.Columns["NameNo"].Visible = false; So im hiding the columns i dont want instead.

[Gilad Green]

@XxXIbbeXxX - If it works for you... IMO it is a shame to go for a work around when the proper solution is possible and simple.

[Ibrahim Aweidah]

I will mark your answer as the answer but please notice that everyone doesn't have the same programming skill as you and things are so obvious always. Have a nice day :)