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.

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<>' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?) — Ibrahim Aweidah, Aug 09 '17 at 08:15

Gilad Green · Accepted Answer · 2017-08-09T07:27:47.817

3

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

edited Aug 09 '17 at 07:27

answered Aug 09 '17 at 07:19

Gilad Green

36,708
7
61
95

Got this error : Severity Code Description Project File Line Suppression State Error CS0266 Cannot implicitly convert type 'System.Linq.IQueryable<>' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?) – Ibrahim Aweidah Aug 09 '17 at 08:21
@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 – Gilad Green Aug 09 '17 at 08:24
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?) – Ibrahim Aweidah Aug 09 '17 at 08:35
@XxXIbbeXxX - Please put some effort into solving your problem. `ImportNameValidationDTO` does not exist yet. You need to create it. – Gilad Green Aug 09 '17 at 08:56
@XxXIbbeXxX - Did you manage? – Gilad Green Aug 10 '17 at 11:17
Nope, but had a workaround. dataGridView_Result.Columns["NameNo"].Visible = false; So im hiding the columns i dont want instead. – Ibrahim Aweidah Aug 10 '17 at 13:30
@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. – Gilad Green Aug 12 '17 at 05:18
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 :) – Ibrahim Aweidah Aug 14 '17 at 06:41

score 3 · Answer 2 · answered Aug 09 '17 at 07:22

3

Simple use Anonymous Types:

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

answered Aug 09 '17 at 07:22

NeoAsh

1,013
1
8
11

To note, this is no longer of type `ImportNameValidation` – Travis J Aug 09 '17 at 07:23

score 2 · Answer 3 · answered Aug 09 '17 at 07:22

2

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

answered Aug 09 '17 at 07:22

Harsh

3,683
2
25
41

You don't need AsEnumerable here. – Travis J Aug 09 '17 at 07:24
@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. – Harsh Aug 09 '17 at 07:26
1

AsEnumerable will cause the entire query to be pulled into memory, and erase the gains of only selecting a subset of the fields. – Travis J Aug 09 '17 at 07:27
@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. – Harsh Aug 09 '17 at 07:28
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: https://dotnetfiddle.net/KdrqtJ . This should work as is. – Travis J Aug 09 '17 at 07:34
@TravisJ It's not about the down vote :) . Here is the problem i am referring to https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query – Harsh Aug 09 '17 at 07:40
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(t => new ImportNameValidation() { ... } )` it could work. – Travis J Aug 09 '17 at 07:46

C# - IQueryable query how to select?

3 Answers3