C# 带有 ExecuteQuery 的 Linq SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17614545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq SELECT with ExecuteQuery
提问by Tom Gullen
I get the error:
我收到错误：
The type 'System.Int32[]' must declare a default (parameterless) constructor in order to be constructed during mapping.
类型“System.Int32[]”必须声明一个默认（无参数）构造函数，以便在映射期间构造。
With the code:
使用代码：
var gamePlayRecord = db.ExecuteQuery<int[]>("SELECT UserID, IPID, GameID FROM ArcadeGames WHERE ID = " + gamePlayRecordID).Single();
var userID = gamePlayRecord[0];
var ipID = gamePlayRecord[1];
var gameID = gamePlayRecord[2];
I know this is wrong, but can someone show me how to do it correctly without needing to create a new object preferably?
我知道这是错误的，但是有人可以告诉我如何正确地做到这一点，而无需最好地创建一个新对象吗？
回答by Bhaarat
It should be something like this
它应该是这样的
var gamePlayRecord = db.ExecuteQuery<ArcadeGames>(@"SELECT UserID, IPID, GameID FROM ArcadeGames WHERE ID = {0}", gamePlayRecordID).Single();
var userID = gamePlayRecord.UserID;
var ipID = gamePlayRecord.IPID;
var gameID = gamePlayRecord.GameID;
Code reference taken from http://msdn.microsoft.com/en-us/library/bb361109.aspx
回答by goric
The ORM, after retrieving the SQL results, tries to create a new instance of the type you specified, and then find properties on that type with the same name as the columns selected in the query. So in your case, it is trying to create a new int[]and then set properties on it called UserId, IPId, and GameId.
ORM 在检索 SQL 结果后，尝试创建您指定类型的新实例，然后在该类型上查找与查询中选择的列同名的属性。所以你的情况，它试图创建一个新的int[]，然后设置属性就可以了叫UserId，IPId和GameId。
There are a few issues here. First, when creating the instance, by default a parameterless constructor is used. int[]doesn't have one that can be called, and this is the error you are seeing. Assuming it did have one, I expect this code would fail after instantiating it when trying to set a property called UserId.
这里有几个问题。首先，在创建实例时，默认使用无参数构造函数。int[]没有可以调用的，这是您看到的错误。假设它确实有一个，我希望这段代码在尝试设置一个名为UserId.
An easy way around this is to create your own class, as others have answered. If you want a way that doesn't need a new type, you could use something like the non-generic Queryclass from the dapperlibrary (see Marc Gravell's answer in thispost). The Entity Framework also seems to provide some similar functionality using the ObjectQueryand DbDataRecordclasses, as shown here.
解决这个问题的一个简单方法是创建自己的类，正如其他人所回答的那样。如果你想要一种不需要新类型的方法，你可以使用Query来自dapper库的非泛型类（参见 Marc Gravell 在这篇文章中的回答）。实体框架也似乎提供了使用一些类似的功能ObjectQuery和DbDataRecord等级，如图所示这里。
回答by slavoo
Result of this query is not int[]but one row with numbers.
此查询的结果不int[]只是一行数字。
not good solution: use for every number:
不好的解决方案：用于每个数字：
int userID = db.ExecuteQuery<int>("SELECT UserID FROM ArcadeGames WHERE ID = " + gamePlayRecordID).Single();
int ipID = db.ExecuteQuery<int>("SELECT IPID FROM ArcadeGames WHERE ID = " + gamePlayRecordID).Single();
int gameID db.ExecuteQuery<int>("SELECT GameID FROM ArcadeGames WHERE ID = " + gamePlayRecordID).Single();
or Create sql query
或创建 sql 查询
db.ExecuteQuery<int>(@"
SELECT UserID FROM ArcadeGames WHERE ID = {0}
UNION ALL
SELECT IPID FROM ArcadeGames WHERE ID = {0}
UNION ALL
SELECT GameID FROM ArcadeGames WHERE ID = {0}",
gamePlayRecordID).ToList();
or Create class ...
或创建类...
回答by Andre van Heerwaarde
I don't think there is a correct way to do it, because it wants to map it to an object with named properties. Even if you are able to instantiate the array, it will fail trying to map it to the named fields.
我认为没有正确的方法可以做到这一点，因为它想将其映射到具有命名属性的对象。即使您能够实例化数组，尝试将其映射到命名字段也会失败。
If you don't want to create a class for it, but are more interested in creating a dynamic object, you can take a look at dapper orm. Install it via nuget. you can do something like this:
如果你不想为它创建一个类，但对创建一个动态对象更感兴趣，你可以看看 dapper orm。通过 nuget 安装它。你可以这样做：
using (var sqlConnection
= new SqlConnection(connectionString))
{
sqlConnection.Open();
IEnumerable products = sqlConnection
.Query("Select * from Products where Id = @Id",
new {Id = 2});
foreach (dynamic product in products)
{
ObjectDumper.Write(string.Format("{0}-{1}", product.Id, product.ProductName));
}
sqlConnection.Close();
}
回答by Andre van Heerwaarde
I think I have misunderstood the question a little bit. But as @goric explained: An ORM mapper wants to map the results to an object. If you don't want an object or class than don't use an ORM mapper, but use the basic SqlDataReader.
我想我有点误解了这个问题。但正如@goric 解释的那样：ORM 映射器想要将结果映射到一个对象。如果你不想要一个对象或类，那么不要使用 ORM 映射器，而是使用基本的 SqlDataReader。
SqlCommand command = new SqlCommand("SELECT UserID, IPID, GameID FROM ArcadeGames WHERE ID = " + gamePlayRecordID, connection);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
var userID = reader[0];
var ipID = reader[1];
var gameID = reader[2];
}
回答by user1429080
Here is and uglyhack for doing this:
这是这样做的丑陋黑客：
var gamePlayRecord = db.ExecuteQuery<string>(
"SELECT cast(UserID as nvarchar(10)) + ';' + cast(IPID as nvarchar(10)) + ';' + cast(GameID as nvarchar(10)) as a FROM ArcadeGames WHERE ID = " + gamePlayRecordID
)
.Single()
.Split(';')
.Select(i => int.Parse(i))
.ToArray();
Works on my machine...
在我的机器上工作...
回答by lrodriguez
Not answering your question directly, but take care of security...
不直接回答你的问题，但要注意安全......
Your code is probablyvulnerable to SQL injection attacks, depending on where comes the value for gamePlayRecordID.
您的代码可能容易受到 SQL 注入攻击，具体取决于 gamePlayRecordID 的值。
To fix, make the SQL query parameterized (or thoroughly validate the input, if comes from "untrusted" sources):
要修复，请使 SQL 查询参数化（或彻底验证输入，如果来自“不受信任”的来源）：
db.ExecuteQuery<int>(@"
SELECT UserID FROM ArcadeGames WHERE ID = {0}
UNION ALL
SELECT IPID FROM ArcadeGames WHERE ID = {0}
UNION ALL
SELECT GameID FROM ArcadeGames WHERE ID = {0}",
gamePlayRecordID).ToList();
as the first solution showed... Many of the solutions provided might have the very same security problem.
正如第一个解决方案所示......提供的许多解决方案可能具有相同的安全问题。
