从DbSet<T>中选择特定字段

Question

我正在使用EF4.1，为了提高性能，我需要从与SQL服务器上的一个视图映射的DbSet中仅选择少数字段。

有什么好的方法吗？

如果从客户端选择字段，则会返回以下错误。

var result = context.Customers.Select(c => new Customer{ Name = c.Name,Age = c.Age}).ToList();

The entity or complex type 'Customer' cannot be constructed in a LINQ to Entities query.
   at System.Data.Objects.ELinq.ExpressionConverter.CheckInitializerType(Type type)
   at System.Data.Objects.ELinq.ExpressionConverter.MemberInitTranslator.TypedTranslate(ExpressionConverter parent, MemberInitExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.AggregateTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3[TResult](IEnumerable`1 sequence)
   at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)

- hazimdikenli

Answer 1

假设您正确设置了与视图的映射，并且 Customers 是您上下文中的一个 DbSet<Customer> 属性。

var result = context.Customers.Select(c => new {c.Name,c.Age, c.AnyOtherProperty});

编辑：展示如何使用自己的类而不是匿名类型。

var result = context.Customers.Select(c => new YourClassName{c.Name,c.Age, c.AnyOtherProperty});

- Darren Lewis

---

我的问题在于尝试以其自身的类类型返回数据。context.Customer.Select(c => new Customer {c.Name, c.Age}); 这会引发错误。 - hazimdikenli

我已经添加了一些额外的代码，以展示它投射到你自己的类中。这段代码仅仅是一个示例，并不意味着可以通过剪切和粘贴在你的环境中工作。如果你没有简单地剪切和粘贴，请提供关于错误更多的信息。 - Darren Lewis

我的问题是尝试将结果集投影到同一类中时。不需要转换，从DbSet<Customer>选择“new Customer”，并指定要选择哪些字段。 - hazimdikenli

Answer 2

如果您想坚持使用lambda表达式，这里有另一种方法来完成它。在这里，您可以使用"where"子句和"select"语句来控制您要选择的内容：

var myResult = context.Customers.Where(c => c.Name== "someName" && c.Age == age).Select(c => c.Name);

for each (var x in myResult)
{
   //do whatever
}

- Tony

Answer 3

在EF 4.1中，这对我来说看起来像一个bug，因为它将其视为SQL聚合函数并尝试解决它，但实际上它是相同的类。您可以尝试以下方法：

var q = context.Customers.Select(c=>new {Name=c.Name, Age=c.Age});

foreach(var x in q)
{
    yield new Customer {Name=c.Name, Age=c.Age};
}

- Akash Kava

---

实际上，该函数返回查询结果，并在其上执行跳过和获取操作，可能是在那里出现了问题。 - hazimdikenli