代码之家 › 专栏 › 技术社区 › Prisoner ZERO

在不带EDMX的LINQ语句中调用标量函数

entity-framework sql c#

Prisoner ZERO · 技术社区 · 7 年前

我试图在ef中调用一个SQL标量函数。我尝试过各种各样的例子,但我不断地得到:

类型Y上指定的方法x无法转换为Linq 到实体存储表达式。

我正在使用…

参考6.1.3
EntityFramework.函数1.4.1

查询:
注意,我在select中调用它…

public IQueryable<MeterDataItem> Query()
{
    var query = from meter in UnitOfWork.Meter
                join meterType in UnitOfWork.MeterType on meter.MeterTypeId equals meterType.Id into meterTypeLEFTJOIN
                    from meterType in meterTypeLEFTJOIN.DefaultIfEmpty()
                join company in UnitOfWork.Company on meter.CompanyId equals company.Id
                join meterPosition in UnitOfWork.EFMMeterPosition on meter.EFMMeterPositionId equals meterPosition.Id into meterPositionLEFTJOIN
                    from meterPosition in meterPositionLEFTJOIN.DefaultIfEmpty()
                join flowType in UnitOfWork.FlowType on meter.FlowTypeId equals flowType.Id into flowTypeLEFTJOIN
                    from flowType in flowTypeLEFTJOIN.DefaultIfEmpty()
                join fluidType in UnitOfWork.FluidType on meter.FluidTypeId equals fluidType.Id into fluidTypeLEFTJOIN
                    from fluidType in fluidTypeLEFTJOIN.DefaultIfEmpty()
                join runStatus in UnitOfWork.RunStatus on meter.RunStatusId equals runStatus.Id into runStatusLEFTJOIN
                    from runStatus in runStatusLEFTJOIN.DefaultIfEmpty()
                join pipeline in UnitOfWork.Pipeline on meter.PipelineId equals pipeline.Id into pipelineLEFTJOIN
                    from pipeline in pipelineLEFTJOIN.DefaultIfEmpty()

                // Device portion
                join device in UnitOfWork.Device on meter.DeviceId equals device.Id into deviceLEFTJOIN
                    from device in deviceLEFTJOIN.DefaultIfEmpty()
                join rtuDevice in UnitOfWork.RTUDevice on device.Id equals rtuDevice.DeviceId into rtuDeviceLEFTJOIN
                    from rtuDevice in rtuDeviceLEFTJOIN.DefaultIfEmpty()

                // Contact portion
                join measureTech in UnitOfWork.User on rtuDevice.MeasurementTechnicianId equals measureTech.Id into measureTechLEFTJOIN
                    from measureTech in measureTechLEFTJOIN.DefaultIfEmpty()
                join commTech in UnitOfWork.User on rtuDevice.CommunicationTechnicianId equals commTech.Id into commTechLEFTJOIN
                    from commTech in commTechLEFTJOIN.DefaultIfEmpty()

                // Connection portion
                join deviceCircuit in UnitOfWork.DeviceCircuit on device.Id equals deviceCircuit.DeviceId into deviceCircuitLEFTJOIN
                    from deviceCircuit in deviceCircuitLEFTJOIN.DefaultIfEmpty()
                join circuit in UnitOfWork.Circuit on deviceCircuit.CircuitId equals circuit.Id
                join circuitConnection in UnitOfWork.CircuitConnection on circuit.Id equals circuitConnection.CircuitId
                join connection in UnitOfWork.Connection on circuitConnection.ConnectionId equals connection.Id

                where
                    deviceCircuit.IsPrimary == true

                select new MeterDataItem()
                {
                    MeterId = meter.Id,
                    MeterNumber = meter.MeterNumber,
                    MeterName = meter.MeterName,
                    MeterTypeId = meterType.Id,
                    MeterTypeName = meterType.MeterTypeName,
                    MeterPositionCategory = meterPosition.EFMMeterPositionCategory,
                    FlowTypeName = flowType.FlowTypeName,
                    FluidTypeCategory = fluidType.FluidTypeCategory,
                    RunStatusCategory = runStatus.RunStatusCategory,
                    PipelineName = pipeline.PipelineName,
                    CompanyName = company.CompanyName,
                    ConnectionValue = GetConnection(circuitConnection.Id, connection.ConnectionTypeName),
                    DeviceId = device.Id,
                    DeviceName = device.DeviceName,
                    MeasurementTechnicianId = measureTech.Id,
                    MeasurementTechnicianFirstName = measureTech.FirstName,
                    MeasurementTechnicianLastName = measureTech.LastName,
                    CommunicationTechnicianId = commTech.Id,
                    CommunicationTechnicianFirstName = commTech.FirstName,
                    CommunicationTechnicianLastName = commTech.LastName,
                    MeterObjectStateName = null,    //<-- Default Value
                    FavoriteId = 0                  //<-- Default Value
                };

    return query.OrderBy(x => x.MeterNumber);
}

CSharp函数:
我猜它找不到函数是因为某种原因…

[Function(FunctionType.ComposableScalarValuedFunction, nameof(svfn_GetMeterConnection), Schema = "dbo")]
[return: Parameter(DbType = "VARCHAR(100)")]
public string svfn_GetMeterConnection([Parameter(DbType = "INT")]int circuitConnectionId, [Parameter(DbType = "VARCHAR(50)")]string connectionTypeName)
{
    ObjectParameter circuitConnectionIdParameter = new ObjectParameter("@CircuitConnectionId", circuitConnectionId);
    ObjectParameter connectionTypeNameParameter = new ObjectParameter("@ConnectionTypeName", connectionTypeName);

    return UnitOfWork.DbContext.ObjectContext().ExecuteFunction<string>(nameof(this.svfn_GetMeterConnection), circuitConnectionIdParameter, connectionTypeNameParameter).SingleOrDefault();
}

SQL函数:
我的所有名称和参数名称引用看起来都正确…

ALTER FUNCTION [dbo].[svfn_GetMeterConnection]
(
    @CircuitConnectionId INT,
    @ConnectionTypeName VARCHAR(50)
)
RETURNS VARCHAR(100)
AS
BEGIN

    DECLARE @Value VARCHAR(100) = NULL;

    -- DIAL-UP
    IF(@ConnectionTypeName = 'Dial-Up')
    BEGIN
        SELECT
            @Value = circuitConnectionConfiguration.ConnectionPropertyValue
        FROM [dbo].[CircuitConnectionConfiguration] circuitConnectionConfiguration
        JOIN [dbo].[ConnectionConfiguration] connectionConfiguration ON connectionConfiguration.Id = circuitConnectionConfiguration.ConnectionConfigurationId
        JOIN [dbo].[ConnectionProperty] connProperty ON connProperty.Id = connectionConfiguration.ConnectionPropertyId
        WHERE
            circuitConnectionConfiguration.CircuitConnectionId = @CircuitConnectionId
            AND connProperty.ConnectionPropertyName = 'Dial-Up Number'
    END

    -- INTERNET PROTOCOL (IP)
    IF(@ConnectionTypeName = 'Internet Protocol (IP)')
    BEGIN
        SELECT @Value = 
            ((SELECT circuitConnectionConfiguration.ConnectionPropertyValue
                FROM [dbo].[CircuitConnectionConfiguration] circuitConnectionConfiguration
                JOIN [dbo].[ConnectionConfiguration] connectionConfiguration ON connectionConfiguration.Id = circuitConnectionConfiguration.ConnectionConfigurationId
                JOIN [dbo].[ConnectionProperty] connProperty ON connProperty.Id = connectionConfiguration.ConnectionPropertyId
            WHERE
                circuitConnectionConfiguration.CircuitConnectionId = @CircuitConnectionId
                AND connProperty.ConnectionPropertyName = 'IP Address') 
            + ':' +
            (SELECT circuitConnectionConfiguration.ConnectionPropertyValue
                FROM [dbo].[CircuitConnectionConfiguration] circuitConnectionConfiguration
                JOIN [dbo].[ConnectionConfiguration] connectionConfiguration ON connectionConfiguration.Id = circuitConnectionConfiguration.ConnectionConfigurationId
                JOIN [dbo].[ConnectionProperty] connProperty ON connProperty.Id = connectionConfiguration.ConnectionPropertyId
            WHERE
                circuitConnectionConfiguration.CircuitConnectionId = @CircuitConnectionId
                AND connProperty.ConnectionPropertyName = 'Port'))
    END

    -- Return the result of the function
    RETURN @Value
END

更新-要回答:
我需要的一些改变包括:

将函数类型更改为FunctionType.ComposableScalarValuedFunction
将dbtype参数字符串更改为小写
确保不要在dbtype声明中包含大小:将vachar(50)更改为varchar
将调用移动到具体的dbContext中
在具体dbContext的onModelCreating中注册函数Convention

具体的数据库上下文:
包括命名空间…

using EntityFramework.Functions;
using StructureMap;
using System.Configuration;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Linq;

public class MeasurementContractsDbContext : BaseDbContext
{
    #region <Constructors>

    [DefaultConstructor]
    public MeasurementContractsDbContext() : base(Settings.ConnectionString.Database.MeasurementContractsDb)
    {
        Database.SetInitializer<MeasurementContractsDbContext>(null);
        Database.CommandTimeout = int.Parse(ConfigurationManager.AppSettings[Settings.Command.TimeoutInterval]);
        Configuration.ProxyCreationEnabled = false;
    }

    #endregion

    #region <Methods>

    [Function(FunctionType.ComposableScalarValuedFunction, nameof(svfn_GetMeterConnection), Schema = "dbo")]
    [return: Parameter(DbType = "varchar")]
    public string svfn_GetMeterConnection(int circuitConnectionId, string connectionTypeName)
    {
        ObjectParameter circuitConnectionIdParameter = new ObjectParameter("CircuitConnectionId", circuitConnectionId);
        ObjectParameter connectionTypeNameParameter = new ObjectParameter("ConnectionTypeName", connectionTypeName);

        return this.ObjectContext().ExecuteFunction<string>(nameof(this.svfn_GetMeterConnection), circuitConnectionIdParameter, connectionTypeNameParameter).SingleOrDefault();
    }

    [Function(FunctionType.ComposableScalarValuedFunction, nameof(svfn_GetCurrentObjectStateName), Schema = "dbo")]
    [return: Parameter(DbType = "varchar")]
    public string svfn_GetCurrentObjectStateName(int contextId, string contextFullName)
    {
        ObjectParameter contextIdParameter = new ObjectParameter("contextId", contextId);
        ObjectParameter contextFullNameParameter = new ObjectParameter("contextFullName", contextFullName);

        return this.ObjectContext().ExecuteFunction<string>(nameof(this.svfn_GetCurrentObjectStateName), contextIdParameter, contextFullNameParameter).SingleOrDefault();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // ADD Functions
        modelBuilder.Conventions.Add(new FunctionConvention<MeasurementContractsDbContext>());

        // ...
    }

    #endregion
}

样品用途:

/// <exception cref="ArgumentNullException">Non-Existent 'Query' value throws this exception</exception>
public IQueryable<MeterDetailDataItem> AuthorizationToFlowMeterDocumentFilter(IQueryable<MeterDetailDataItem> query)
{
    if (query == null)
        throw new ArgumentNullException("Query");

    string contextFullName = typeof(AuthorizationToFlowMeterDocument).FullName;

    // Get the ATF (if it exists)
    var filteredQuery = (from dataitem in query //<-- QUERY
                        join document in UnitOfWork.Document on dataitem.RequestToFlowMeterDocumentId equals document.ParentId

                         // TODO: Figure out if you can move svfn_GetCurrentObjectStateName into an Algorythm class that can be injected
                         // SQL Function
                         let objectStateName = ((MeasurementContractsDbContext)UnitOfWork.DbContext).svfn_GetCurrentObjectStateName(document.Id, contextFullName)

                        select new MeterDetailDataItem()
                        {
                            MeterId = dataitem.MeterId,
                            RequestToFlowMeterDocumentId = dataitem.RequestToFlowMeterDocumentId,
                            RequestToFlowMeterDocumentObjectStateName = dataitem.RequestToFlowMeterDocumentObjectStateName,
                            AuthorizationToFlowMeterDocumentId = document.Id,
                            AuthorizationToFlowMeterDocumentObjectStateName = objectStateName,
                            FirstDeliveryNoticeDocumentId = dataitem.FirstDeliveryNoticeDocumentId,
                            FirstDeliveryNoticeDocumentObjectStateName = dataitem.FirstDeliveryNoticeDocumentObjectStateName,
                            FavoriteId = dataitem.FavoriteId
                        });

    return filteredQuery.OrderBy(x => x.MeterId);
}

1 回复 | 直到 7 年前

Ivan Stoev 7 年前

看起来你在用 EntityFramework.Functions 包裹。那就看看 Function 主题。您正在使用 标量值函数,不可组合 根据包裹作者

可以像上面的其他方法一样直接调用

但是

但是,由于它被指定为不可组合的,因此它不能由LINQ中的实体框架转换为实体查询。

和

这是通过实体框架的设计实现的。

而你需要的是 标量值函数,可组合 ,其中

在Linq to Entities查询中工作,但不能直接调用

很快,由于您在Linq to Entities查询中使用它,因此使用 FunctionType.ComposableScalarValuedFunction 里面 Function 注释。由于它不是直接可调用的,所以它不需要方法体,所以您可以简单地抛出异常:

[Function(FunctionType.ComposableScalarValuedFunction, nameof(svfn_GetMeterConnection), Schema = "dbo")]
public string svfn_GetMeterConnection(int circuitConnectionId, string connectionTypeName)
{
    throw new NotSupportedException();
}

别忘了注册链接中所示的函数,否则它们将不起作用,您将继续获得 NotSupportedException :

modelBuilder.Conventions.Add(new FunctionConvention<TheClassContainingTheFunction>());