How to Select from table records except some columns

Question

I have a entity database, created from sql database. I need to show record on datagridview, i am using this code.

DBEntities db = new DBEntities();
dataGridView1.DataSource = db.Agent.Select(x => new { Name = x.Name, Second_Name = x.Second_Name}).ToList();

It's example, real agent table contain around 10 columns, and i need to show all, exept 'id'. If i do same for every 8 columns, become a long and senseless row. How to do it more obliviuous and good.

Robert Harvey · Accepted Answer · 2014-12-01T16:37:31.403

4

If you don't want to use an anonymous type to specify the fields you want, you can:

Live with having the ID in the result set, or
Include all of the columns in the Select except for the ID, or
Use a mapping library, like AutoMapper.

There's no Select Except statement in Linq. However, you can use this extension method to accomplish the same thing:

/// <summary>
/// Returns all fields/properties from <paramref name="source"/> except for the field(s)/property(ies) listed in the selector expression.
/// </summary>
public static IQueryable SelectExcept<TSource, TResult>( this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector )
{
    var newExpression = selector.Body as NewExpression;

    var excludeProperties = newExpression != null
            ? newExpression.Members.Select( m => m.Name )
            : new[] { ( (MemberExpression)selector.Body ).Member.Name };

    var sourceType = typeof( TSource );
    var allowedSelectTypes = new Type[] { typeof( string ), typeof( ValueType ) };
    var sourceProperties = sourceType.GetProperties( BindingFlags.Public | BindingFlags.Instance ).Where( p => allowedSelectTypes.Any( t => t.IsAssignableFrom( ( (PropertyInfo)p ).PropertyType ) ) ).Select( p => ( (MemberInfo)p ).Name );
    var sourceFields = sourceType.GetFields( BindingFlags.Public | BindingFlags.Instance ).Where( f => allowedSelectTypes.Any( t => t.IsAssignableFrom( ( (FieldInfo)f ).FieldType ) ) ).Select( f => ( (MemberInfo)f ).Name );

    var selectFields = sourceProperties.Concat( sourceFields ).Where( p => !excludeProperties.Contains( p ) ).ToArray();

    var dynamicSelect = 
            string.Format( "new( {0} )",
                    string.Join( ", ", selectFields ) );

    return selectFields.Count() > 0
        ? source.Select( dynamicSelect )
        : Enumerable.Empty<TSource>().AsQueryable<TSource>();
}

Further Reading
Use SelectExcept When You Are Too Lazy to Type

edited Dec 01 '14 at 16:37

answered Nov 29 '14 at 18:55

Robert Harvey

178,213
47
333
501

I do not want to be rude, but this is wrong. Through anonymous types you can select only some columns and the Id column can be excluded. – Ognyan Dimitrov Dec 01 '14 at 09:10
1

@OgnyanDimitrov: The OP stated specifically in his question that he did not want to do this. That's why I did not include it in my answer. – Robert Harvey Dec 01 '14 at 15:26
I said that because (1) we can live without the Id column in the result set and (2) display it properly in the daraGridView (3) keeping the query to the best possible (4) and all this without the jitter of anonymous types (by using AutoMapper or another mapper). – Ognyan Dimitrov Dec 01 '14 at 16:07
@OgnyanDimitrov Adding the complexity of an additional library (which very likely uses Reflection to do its work) just to eliminate an ID doesn't seem like a reasonable tradeoff to me. – Robert Harvey Dec 01 '14 at 16:15
You are right :) Not a good tradeoff. But it is widely used and the learning curve is not steep. Another way I think of is that you do not need to add a library. This is an option. You can make the mapping by hand in static method in order not to do visual jitter. – Ognyan Dimitrov Dec 01 '14 at 16:19
That is the story :) Upvoting :) – Ognyan Dimitrov Dec 01 '14 at 17:51
@RobertHarvey, many thanks for this suggestion, I am trying to use the above method to remove only two fields from 15 fields and getting an error here `return selectFields.Length > 0 ? source.Select(dynamicSelect) : Enumerable.Empty().AsQueryable();` the type arguments of the method (method signature) cannot be inferred from usage, try specifying the arguments explicitly – Glory Raj Apr 01 '21 at 18:09

score 1 · Answer 2 · answered Nov 29 '14 at 19:01

1

Why don't you use automapper to convert the entity object to a domain object?

Mapper.CreateMap<Agent, AgentDto>();
dataGridView1.DataSource = db.Agent.Select(x => Mapper.Map<AgentDto>(x)).ToList();

The AgentDto will contain all the fields except 'Id'

answered Nov 29 '14 at 19:01

maruthu chandrasekaran

168
6

score 0 · Answer 3 · edited May 23 '17 at 12:27

This question is answered here and it is definitely possible. You can cast the selected columns again into enumerable of the original object if the undesired properties are nullable or you can use anonymous.

For example :

Full query tested in LINQ Pad agains real table

from l in Lessons select new { l.Id, l.Description, l.LanguageId,
l.CreatedOn }

has resulting SQL :

SELECT [t0].[Id], [t0].[Description], [t0].[LanguageId], [t0].[CreatedOn]
FROM [Lessons] AS [t0]

The cut version tested in LINQ Pad with less columns

from l in Lessons select new { l.CreatedOn }

has resulting SQL :

SELECT [t0].[CreatedOn]
FROM [Lessons] AS [t0]

After all that you can hide a column in the dataGridView.

If you do not want to write the anonymous object you can use AutoMapper as @ maruthu chandrasekaran suggested.

This way you will be able to server your linq query reduced and map it to your object in one row without the jitter of anonymous object. You can create a DTO object too. Using Entity-EntityDto mappings with AutoMapper is an easy job.

How to Select from table records except some columns

3 Answers3

Linked