System.Data.Entity.Core.EntityCommandExecutionException [英] System.Data.Entity.Core.EntityCommandExecutionException
问题描述
我有这个代码:它的目标是获取不属于Incidents表的ClientProductVehicles。 SQL查询在SQL Server上运行完美,但是当我把它放在这里它会返回以下错误
I have this code: Which its objective is to get ClientProductVehicles that are not part of the Incidents table. The SQL query runs perfectly on SQL Server but when i put it here it returns the error below
public List< ClientProductVehicle> GetVehicleServiceDue(int clientId,int productId,DateTime date)
{
&NBSP; var reportDate = date.ToShortDateString();
$
&NBSP; var query = $ @" SELECT t.Name AS'Department or Division',c.RegistrationNumber AS'车辆登记号码'
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ,v.Model +''+ v.Variant AS'Model / Vehicle Description',c.ChasisNumber AS'VIN / Chassis Number'$
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ,c.FirstRegistrationDate AS'首次注册日期',Od.Reading AS'里程表阅读'
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ,Od.ReadingDate AS'里程表阅读日期',Vc.Description为'车辆类别'
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;来自clientproductvehicles c加入客户t &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ON c.TargetClientId = t.ClientId JOIN Vehicles v
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ON c.vehicleid = v.vehicleid加入VehicleCategories Vc
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ON c.VehicleCategoryId = Vc.VehicleCategoryId LEFT JOIN OdometerReadings Od
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ON c.Clientproductvehicleid = Od.ClientProductVehicleId
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;在哪里c.ClientId = {clientId}
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; AND c.ProductId = {productId}
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; AND(c.LastOdometerReading BETWEEN(c.ServiceInterval - 500)AND c.ServiceInterval)
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;与&NBSP; (convert(varchar(10),Od.ReadingDate,103)LIKE convert(varchar(10),{reportDate},103))
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; AND(c.VehicleCategoryId = 1)
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; AND c.ClientProductVehicleId
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;不在&bbsp; b $ b &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; (选择i.ClientProductVehicleId
来自事故i
JOIN IncidentStates iSt
ON i.IncidentId = iSt.IncidentId
WHERE(i.CategoryId = 4或i.Description LIKE'%km%')
AND IncidentStatusId = 1)
ORDER BY'里程表读数日期'DESC";
&NBSP;列表与LT; ClientProductVehicle> vehicleServiceDue = _moiFleetContext.Db.SqlQuery< ClientProductVehicle>(查询).ToList();
&NBSP; return vehicleServiceDue;
}
public List<ClientProductVehicle> GetVehicleServiceDue(int clientId, int productId, DateTime date)
{
var reportDate = date.ToShortDateString();
var query = $@" SELECT t.Name AS 'Department or Division',c.RegistrationNumber AS 'Vehicle Registration Number'
,v.Model + ' ' + v.Variant AS 'Model/ Vehicle Description',c.ChasisNumber AS 'VIN/ Chassis Number'
,c.FirstRegistrationDate AS 'First Registration Date',Od.Reading AS 'Odometer Reading'
,Od.ReadingDate AS 'Odometer Reading Date',Vc.Description as 'Vehicle Category'
FROM clientproductvehicles c JOIN Clients t
ON c.TargetClientId=t.ClientId JOIN Vehicles v
ON c.vehicleid=v.vehicleid JOIN VehicleCategories Vc
ON c.VehicleCategoryId=Vc.VehicleCategoryId LEFT JOIN OdometerReadings Od
ON c.Clientproductvehicleid=Od.ClientProductVehicleId
WHERE c.ClientId={clientId}
AND c.ProductId={productId}
AND (c.LastOdometerReading BETWEEN (c.ServiceInterval - 500) AND c.ServiceInterval)
AND (convert(varchar(10), Od.ReadingDate, 103) LIKE convert(varchar(10), {reportDate}, 103))
AND ( c.VehicleCategoryId = 1)
AND c.ClientProductVehicleId
NOT IN
(SELECT i.ClientProductVehicleId
FROM Incidents i
JOIN IncidentStates iSt
ON i.IncidentId = iSt.IncidentId
WHERE (i.CategoryId = 4 or i.Description LIKE '%km%')
AND IncidentStatusId = 1)
ORDER BY 'Odometer Reading Date' DESC";
List<ClientProductVehicle> vehicleServiceDue = _moiFleetContext.Db.SqlQuery<ClientProductVehicle>(query).ToList();
return vehicleServiceDue;
}
每次运行时都会出现此错误:
And i get this error every time i run it:
推荐答案
我有这样的代码:它的目标是获取不属于事件表的ClientProductVehicles。 SQL查询在SQL Server上运行完美,但是当我把它放在这里它会返回以下错误
除非所有结果都不能直接通过EF运行存储过程sproc的一部分来自EF模型上的实体。
You can't run a stored procedure through EF directly unless all the results of the sproc are derived from entities on the EF model.
你唯一能做的就是使用ADO.NET,SQL命令对象,一个数据存储器和加载的自定义对象进入通用集合列表< T>。
The only thing you can do is use ADO.NET, SQL Command Objects, a datareder and custom object loaded into a generic collection List<T>.
这篇关于System.Data.Entity.Core.EntityCommandExecutionException的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!