实体框架原始SQL查询选择未知列（未知返回类型） [英] Entity Framework Raw SQL query selecting unknown columns (unknown return type)

问题描述

当我不知道需要返回的类型时，我无法在实体数据集上运行raw sql。

这种情况是页面生成基于用户选择的选项（构建语句的选择和其中部分），
然后尝试使用以下方式拉取它们：$ /

  string sSQL =SELECT+ sSelect +FROM dbo.Staff; 
 
 if（！string.IsNullOrWhiteSpace（sWhere））{sSQL + =WHERE+ sWhere; } 
 
 DAL.AcdmContext ds = new DAL.AcdmContext（）; 
 var resultSet = ds.Database.SqlQuery（sSQL）.ToList（）; 
  

似乎是它不能对结果集使用匿名类型。问题是因为s​​elect语句是在生成的，我不能事先为结果集生成类或类似的组合。

我可以通过

  var resultSet = ds.Database.SqlQuery< string>（sSQL）提取每列的查询或一列.ToList（）; 
  

但是我无法找到一种方法来处理任何其他数量的列 - 我尝试过< string []> 和 IEnumerable< string>

我的最后一个想法是拉出整套列，并将其过滤到事实之后我需要的，但这似乎是浪费的。有没有更好的解决方案？

（哦，在任何人说之前，我知道我应该在查询中使用参数 - 我暂时把他们拿出来试试看工作）

谢谢！

解决方案

按照您的方式执行查询 http： //www.codeproject.com/Articles/206416/Use-dynamic-type-in​​-Entity-Framework-SqlQuery

但即使它的工作原理认真建议，建立一个这样的查询即使你使用params也是可怕的。更好的是正确地映射实体，并使用LINQ to Entities为您生成查询。

是的，它会带来您不需要的列，但除非您选择了数千或数百万条记录，否则这些记录可能对性能有很大的影响，如果我没有错误的话，更好的（和更可维护的）代码和linq对于实体将会照顾安全问题，如Sql Injection

I'm having trouble running raw sql against an entity dataset when I dont know the type that needs to be returned.

The scenario is that the page generates the sql on the fly, based on the options the user selects (Building both the 'Select' and 'Where' part of the statements) then tries to pull them using:

        string sSQL = "SELECT " + sSelect + " FROM dbo.Staff ";

        if (!string.IsNullOrWhiteSpace(sWhere)) { sSQL += "WHERE " + sWhere; }

        DAL.AcdmContext ds = new DAL.AcdmContext();
        var resultSet = ds.Database.SqlQuery(sSQL).ToList();

It seems to be that it can't use an anonymous type for the resultset. The problem is that because the select statement is generated on the fly, I can't generate a class or similar for the resultset beforehand - there's too many combinations.

I can pull a query of every column, or with one column by

        var resultSet = ds.Database.SqlQuery<string>(sSQL).ToList();

but I cant find a way to do it with any other number of columns - i've tried as <string[]> and IEnumerable<string>

My last thought is to pull the whole set of columns and filter it to what I need after the fact, but that just seems wasteful. Is there a better solution?

(Oh, and before anyone says, I know I should be using params in the query - I temporarily took them out to try and get this working)

Thanks!

解决方案

There maybe a possible solution to do the query the way your trying to do http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery

But even if it works i seriously advise against it, building a query like that is horrible even if you use params. Much better to just map the entity normally and use LINQ to Entities to generate the query for you.

Yes it will bring columns you don't need but unless you are selecting many thousands or millions of records it probably wont have much of an impact on performance and it will much better (and much more maintainable) code and linq to entities will, if I'm not mistaken, take care security problems like Sql Injection

这篇关于实体框架原始SQL查询选择未知列（未知返回类型）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！