Selecting Specific Columns Using Linq: What Gets Transferred

Selecting specific columns using linq: What gets transferred?

You don't need to create new classes to select few columns from a table. You can use anonymous types for that.

var qry = from b in dc.Blobs
          orderby b.RowVersion descending
          select new { b.Id, b.Size, b.Signature, b.RowVersion};

return qry.ToList();

Only selected columns are transferred. There is no difference between using plain SQL and using LINQ to SQL. When you are executing LINQ query, it is converted to plain SQL and executed. Then result is mapped to your objects.

You can use SQL Server Profiler to see what query was generated and executed on server. Also you can use LINQPad to see what SQL will be generated from your query. In your case query will be same either you use BlobDetails or anonymous object:

SELECT [t0].[Id], [t0].[Size], [t0].[Signature], [t0].[RowVersion]
FROM [Blobs] AS [t0]
ORDER BY [t0].[RowVersion] DESC

Return selected specified columns

If BlobDetails isn't the LINQ entity, then you can do it directly:

var qry = from b in dc.Blobs
          orderby b.RowVersion descending
          select new BlobDetails {
              Id = b.Id, Size = b.Size,
              Signature = b.Signature, RowVersion = b.RowVersion};

return qry.ToList();

However; if BlobDetails is a LINQ entity, you need to use subtrefuge:

var qry = from b in dc.Blobs
          orderby b.RowVersion descending
          select new {b.Id, b.Size, b.Signature, b.RowVersion};

var typedQry = from b in qry.AsEnumerable()
               select new BlobDetails {
                  Id = b.Id, Size = b.Size,
                  Signature = b.Signature, RowVersion = b.RowVersion};
return typedQry.ToList();

The AsEnumerable breaks the LINQ composition, making it work.

How to Select from table records except some columns

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

1. Live with having the ID in the result set, or
2. Include all of the columns in the Select except for the ID, or
3. 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

Select all columns on an object with Linq

If you want to avoid anonymous types and get everything, why not just return an IEnumerable of the original transaction item?

var transactions = from t in db.Transactions
                        where t.SellingPrice != 0 
                        select t;

Exclude a column from a select using LINQ

Specify each column that you do want in your select statement:

var users = from u in context.Users

        where u.UserId == userId

        select u.UserId, u.Watever, etc... ;

How to return the result set with columns with Linq

You cannot return an anonymous type from a function, they are strictly "inline" classes. When you return it, the foreach loop will only be able to interpret the result as an plain object. I guess you could use reflection to query the property names and values, however it seems much more straight forward to define a data transfer type to hold the results.

See this question, and this blog post.

So you could create a simple struct or class:

public class MyDataResult
{
    public object Column1 { get; set; }
    public object Column2 { get; set; }
}

Then modify your query in the function:

public static IQueryable<MyDataResult> GetInfo(MyEntityModel oEntityModel)
{
  var query =
    (from t in oEntityModel.Table1
     from u in t.Table2
     where t.Status == true &&
     u.Status == true
     select new MyDataResult
     {
       Column1 = t.Column1,
       Column2 = u.Column2
     })
  return query;
}

Something like that should work. Note that I used "object" for the properties in MyDataResult. I don't know the types of the columns you are returning, you should use the actual types in order to get full intellisense.

---

Related Topics