实体框架如何管理将查询结果映射到匿名类型?
考虑以下示例 LINQ to 实体查询
from history in entities.foreignuserhistory
select new { history.displayname, login=history.username, history.foreignuserid }
ToTraceString()
返回字符串如下所示:
SELECT "Extent1"."foreignuserid" AS "foreignuserid",
"Extent1"."displayname" AS "displayname",
"Extent1"."username" AS "username"
FROM "integration"."foreignuserhistory" AS "Extent1"
对我来说,问题是列的顺序与查询不同,并且不采用像 login
这样的别名在示例中。实体框架在哪里存储匿名类型的映射信息?
背景:我将使用 LINQ to 实体来开发插入和选择操作以进行批量操作。
更新: 除了未知的列到属性映射算法之外,使用 select 插入并不难。我们可以使用元数据获取目标 ObjectSet
的表和列名称,构建 INSERT INTO tableName (column_name1, …)
sql 语句字符串,然后附加一些 ObjectQuery.ToTraceString< /code> SELECT 语句。然后使用
((EntityConnection)ObjectContext.Connection).StoreConnection 创建包含结果文本的
DbCommand
,并从 ObjectQuery
填充命令的参数。所以问题是在插入和选定的记录中找到匹配的列顺序。
Consider the following example LINQ to entity query
from history in entities.foreignuserhistory
select new { history.displayname, login=history.username, history.foreignuserid }
ToTraceString()
return string looks like:
SELECT "Extent1"."foreignuserid" AS "foreignuserid",
"Extent1"."displayname" AS "displayname",
"Extent1"."username" AS "username"
FROM "integration"."foreignuserhistory" AS "Extent1"
The problem for me is that columns come in different order from query and do not take aliases like login
in the example. Where does Entity Framework store mapping information for anonymous types?
Background: I'm going to develop insert with select operation using LINQ to entity for mass operations.
Update:
Insert with select is not that hard except for an unknown column to property mapping algorithm. One can get table and column names for destination ObjectSet
using metadata, build INSERT INTO tableName (column_name1, …)
sql statement string and then append some ObjectQuery.ToTraceString
SELECT statement. Then create a DbCommand
with resulting text using ((EntityConnection)ObjectContext.Connection).StoreConnection
and fill command’s parameters from ObjectQuery
. So the problem is to find matching column order in inserted and selected records.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
这是我的解决方案,包括私有部分和内部部分。它通过反射进入缓存的查询计划,该计划将在
ToTraceString
调用或查询执行后存在,以获取所谓的_columnMap
。列映射包含ScalarColumnMap
对象,这些对象按照匿名对象属性的顺序排列,并通过ColumnPos
属性指向相应的列位置。我认为可以放宽一些断言来检查不确切的类型,而是包含必要属性的基本类型。
有没有不反思的解决方案?
Here’s my solution all the way down of privates and internals. It travels with reflection into cached query plan which will exist after
ToTraceString
call or query execution to get what is called_columnMap
. Column map containsScalarColumnMap
objects going in the order of anonymous object’s properties and pointing to the corresponding column position withColumnPos
property.I think some assertions can be relaxed to check not the exact type but base type containing necessary property.
Is there a solution without reflection?
列在查询中如何使用别名并不重要,它们的顺序也不重要。实体框架处理用每个结果填充匿名类型的新实例,这就是您获得诸如
login
之类的别名的地方。附带说明一下,我认为实体框架可能并不像您想象的那样工作。您无法像使用普通 SQL 查询那样在单个操作中执行选择/插入。实体框架将执行您的选择,返回结果,使用这些结果创建实体的新实例(或者在您的情况下,匿名类型),然后您必须使用每个结果来创建目标的新实例类型,将每一项添加到实体/对象上下文中,最后调用实体/对象上下文上的保存更改。这将导致为您添加的每个新实体执行单独的插入语句。
如果您想在单个操作中完成所有操作,而不需要为每条记录实例化新实体,则需要使用在上下文中映射的存储过程,或者使用
ObjectContext.ExecuteStoreCommand
更新:根据您的回答,您真正涉及的更接近元编程,它比实际使用实体框架更依赖于实体模型。我不知道您使用的是哪个版本的 EF(EF 4.0?4.1 w/代码优先和 DbContext?),但我在 EF 4.0 中使用 C# POCO 模板取得了很大成功(POCO 模板是从在线视觉工作室图库下载)。它使用 T4 模板从 .edmx 数据模型生成 POCO 类。在 T4 模板中,您可以向上下文添加方法,这些方法本质上会调用 ExecuteStoreCommand,但不同之处在于您可以生成基于数据模型执行的查询。这样,每当您的数据模型发生更改时,您的查询都会与更改保持同步。
How the columns are aliased in the query shouldn't matter, and neither should their order. Entity Framework handles populating a new instance of your anonymous type with each result, and that's where you get the alias like
login
.As a side note, I think Entity Framework may not work quite how you think. You can't do a select/insert in a single operation like you can using a normal SQL query. Entity Framework will execute your select, return back the results, use those results to create new instances of your entities (or in your case, an anonymous type), and you would then have to use each result to create a new instance of your target type, adding each one to your entity/object context, and finally call save changes on your entity/object context. This will cause an individual insert statement to be executed for each new entity that you've added.
If you want to do it all in a single operation without instantiating a new entity for every record, you'll need to either use a stored procedure that you map in your context, or else execute an in-line SQL query using
ObjectContext.ExecuteStoreCommand
UPDATE: Based on your responses, what you're really getting into is closer to meta-programming that relies on your entity model more so than actually using entity framework. I don't know what version of EF you're using (EF 4.0? 4.1 w/ code first and DbContext?), but I've had a lot of success using the C# POCO template with EF 4.0 (the POCO template is a download from the online visual studio gallery). It uses a T4 template to generate POCO classes from the .edmx data model. In your T4 template, you could add methods to your context that would essentially call
ExecuteStoreCommand
, but the difference would be you can generate the query that gets executed based on your data model. That way any time your data model changes, your query would stay in sync with the changes.更新了 EF 4.4 (5-RC) 完整帖子对此的思考
http://imaginarydevelopment.blogspot.com/2012/06/compose-complex-inserts-from-select.html
使用此功能/逻辑进行批量插入提供一些参数的选择
Updated the reflection on this for EF 4.4 (5-RC)
full post at http://imaginarydevelopment.blogspot.com/2012/06/compose-complex-inserts-from-select.html
using this functionality/logic for doing a bulk insert from a select with some parameters provided