带有包含Xml的实体框架和视图的WCF数据服务

本文关键字：视图 WCF 数据 服务 实体 包含 Xml 框架 | 更新日期: 2023-09-27 18:18:24

我们有一个包含一些XML字段的SQL server视图。我们首先使用实体框架(v5)数据库创建了一个DBContext。我们添加了一个WCF数据服务并将其分配给DBContext，如下所示:

       public class ProductsDBSvc : DataService<ProductsDBEntities>
       {
         config.UseVerboseErrors = true;
         config.SetEntitySetPageSize("vw_Release", 100);
         config.SetEntitySetAccessRule("vw_Release", EntitySetRights.AllRead);
       }

使用浏览器，我们调用http://localhost:60606/Products/vw_Release，但它返回以下错误:

<m:internalexception>
<m:message>
The XML data type cannot be compared or sorted, except when using the IS NULL operator.
</m:message>
<m:type>System.Data.SqlClient.SqlException</m:type>
<m:stacktrace>
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,         Boolean breakConnection, Action`1 wrapCloseInAction)
 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
 at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
 at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
 at System.Data.SqlClient.SqlDataReader.get_MetaData()
 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,     RunBehavior runBehavior, String resetOptionsString)
 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
 at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
 at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
 at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
 at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
</m:stacktrace>
</m:internalexception>

错误意味着我们不能在order by子句中使用XML列，这是一个SQL Server错误。而我们从来没有在查询中要求order by。

所以我们激活SQL分析器，我们发现以下信息:使用实体框架的OData WCF服务引用视图(而不是表)发送一条SQL语句，其中包括每个列的order by，如下所示:

  SELECT 
  [vw_Release].[ID] AS [ID], 
  [vw_Release].[TypeNameXml] AS [TypeNameXml], 
  [vw_Release].[StatusNameXml] AS [StatusNameXml], 
  [vw_Release].[Created] AS [Created], 
  [vw_Release].[Modified] AS [Modified], 
  FROM [dbo].[vw_Release] AS [vw_Release]) AS [Extent1]
  )  AS [Project1]
  ORDER BY [Project1].[Created] ASC, 
  [Project1].[ID] ASC, [Project1].[Modified] ASC,
  [Project1].[StatusNameXml] ASC, [Project1].[TypeNameXml] ASC

2-获取相同的信息，但只是从表而不是视图中读取:

  SELECT 
  [Release].[ID] AS [ID], 
  [Release].[TypeNameXml] AS [TypeNameXml], 
  [Release].[StatusNameXml] AS [StatusNameXml], 
  [Release].[Created] AS [Created], 
  [Release].[Modified] AS [Modified], 
  FROM [dbo].[Release] AS [vw_Release]) AS [Extent1]
  ORDER BY [Project1].[Created] ASC, 
  [Extent1].[ID] ASC

我们所做的一切都是开箱即用的功能，可以很容易地复制。是否有一种方法可以告诉WCF数据服务不包括所有列的排序?

带有包含Xml的实体框架和视图的WCF数据服务

我认为这个问题与配置有关。SetEntityPage size，你能试着不设置它看看会发生什么吗

WCF数据服务(OData)， settentitysetpagesize和自定义操作的问题

相关文章：