EF6 Linq to Sql列别名超过30个字符

m3eecexj  于 2023-09-28  发布在  其他
关注(0)|答案(1)|浏览(109)

我正在将我们的数据访问层从Telerik ORM（已弃用）转换为EF 6。它连接到Oracle 12c数据源，限制为30个字符。有些表名和列名正好是30个字符（如下例所示），有些则更短。
Linq to Sql查询已经存在，并且可以与Telerik一起查找，因为它使用别名作为t1，t2等列名。而EF显然将原始列名复制为别名。在大多数情况下，这是可行的，除了代码表连接，其中代码表的PK和父表的FK完全相同，然后它将1附加到列alais的末尾，从而使其成为31个字符。正如你可以猜到Oracle抱怨。
我需要找到一种方法来强制EF使用更短的别名，有什么方法可以做到这一点吗？
以下是Linq查询：

var dbElevationsHistory = (from elevationHist in dbContext.ElevationDataReadingJournals
join it in dbContext.MeasurementIssueTypes on elevationHist.MeasurementIssueTypeId equals it.MeasurementIssueTypeId into iType
from issueType in iType.DefaultIfEmpty()
join mt in dbContext.ElevationMeasureMethodTypes on elevationHist.ElevationMeasureMethodTypeId equals mt.ElevationMeasureMethodTypeId into mType
from methodType in mType.DefaultIfEmpty()
join at in dbContext.ElevationAccuracyTypes on elevationHist.ElevationAccuracyTypeId equals at.ElevationAccuracyTypeId into aType
from accuracyType in aType.DefaultIfEmpty()
join cAgency in dbContext.Organizations on elevationHist.CoopAgencyOrganizationId equals cAgency.OrganizationId into foundAgencies
from coopAgency in foundAgencies.DefaultIfEmpty()
where elevationHist.StationId == wellKey
select new { elevationHist, issueType, accuracyType, methodType, coopAgency })
   .OrderByDescending(eh => eh.elevationHist.ModifiedDate)
   .OrderByDescending(eh => eh.elevationHist.ElevationDataReadingJournalId);

它生成的SQL查询：

SELECT 
"Project1"."C1" AS "C1", 
"Project1"."EWM_ELEVATION_DATA_READ_JRL_ID" AS "EWM_ELEVATION_DATA_READ_JRL_ID", 
"Project1"."EWM_ELEVATION_DATA_READING_ID" AS "EWM_ELEVATION_DATA_READING_ID", 
"Project1"."CRUD_TYPE" AS "CRUD_TYPE", 
"Project1"."CRUD_DT" AS "CRUD_DT", 
"Project1"."MEASUREMENT_DT" AS "MEASUREMENT_DT", 
"Project1"."ORG_ID" AS "ORG_ID", 
"Project1"."EWM_MEASUREMENT_ISSUE_TYPE_ID" AS "EWM_MEASUREMENT_ISSUE_TYPE_ID", 
"Project1"."EWM_STATION_ID" AS "EWM_STATION_ID", 
"Project1"."EWM_ELEV_MEASURE_METHOD_TYP_ID" AS "EWM_ELEV_MEASURE_METHOD_TYP_ID", 
"Project1"."EWM_ELEVATION_ACCURACY_TYPE_ID" AS "EWM_ELEVATION_ACCURACY_TYPE_ID", 
"Project1"."REFERENCE_POINT_ELEVATION" AS "REFERENCE_POINT_ELEVATION", 
"Project1"."GROUND_SURFACE_ELEVATION" AS "GROUND_SURFACE_ELEVATION", 
"Project1"."WATER_SURFACE_READING" AS "WATER_SURFACE_READING", 
"Project1"."REFERENCE_POINT_READING" AS "REFERENCE_POINT_READING", 
"Project1"."MANDATORY_READING" AS "MANDATORY_READING", 
"Project1"."COMMENTS" AS "COMMENTS", 
"Project1"."MODIFIED_DATE" AS "MODIFIED_DATE", 
"Project1"."MODIFIED_USER" AS "MODIFIED_USER", 
"Project1"."MODIFIED_PROC" AS "MODIFIED_PROC", 
"Project1"."COOPERATING_AGENCY_ORG_ID" AS "COOPERATING_AGENCY_ORG_ID", 
"Project1"."APPL_ID" AS "APPL_ID", 
"Project1"."SUBMISSION_DATE" AS "SUBMISSION_DATE", 
"Project1"."EWM_MEASUREMENT_ISSUE_TYPE_ID1" AS "EWM_MEASUREMENT_ISSUE_TYPE_ID1", 
"Project1"."EWM_MEASURE_ISSUE_TYPE_CODE" AS "EWM_MEASURE_ISSUE_TYPE_CODE", 
"Project1"."EWM_MEASURE_ISSUE_TYPE_DESC" AS "EWM_MEASURE_ISSUE_TYPE_DESC", 
"Project1"."EWM_MEASURE_ISSUE_TYPE_ACTV" AS "EWM_MEASURE_ISSUE_TYPE_ACTV", 
"Project1"."EWM_MEASURE_ISSUE_TYPE_ORDER" AS "EWM_MEASURE_ISSUE_TYPE_ORDER", 
"Project1"."EWM_MEASURE_ISSUE_TYPE_CLASS" AS "EWM_MEASURE_ISSUE_TYPE_CLASS", 
"Project1"."MODIFIED_DATE1" AS "MODIFIED_DATE1", 
"Project1"."MODIFIED_USER1" AS "MODIFIED_USER1", 
"Project1"."MODIFIED_PROC1" AS "MODIFIED_PROC1", 
"Project1"."APPL_ID1" AS "APPL_ID1", 
"Project1"."EWM_ELEVATION_ACCURACY_TYPE_ID1" AS "EWM_ELEVATION_ACCURACY_TYPE_ID1", 
"Project1"."EWM_ELEVATION_ACCURACY_DESC" AS "EWM_ELEVATION_ACCURACY_DESC", 
"Project1"."EWM_ELEVATION_ACCURACY_ACTV" AS "EWM_ELEVATION_ACCURACY_ACTV", 
"Project1"."EWM_ELEVATION_ACCURACY_ORDER" AS "EWM_ELEVATION_ACCURACY_ORDER", 
"Project1"."MODIFIED_DATE3" AS "MODIFIED_DATE2", 
"Project1"."MODIFIED_USER3" AS "MODIFIED_USER2", 
"Project1"."MODIFIED_PROC3" AS "MODIFIED_PROC2", 
"Project1"."EWM_ELEVATION_ACCURACY_CD" AS "EWM_ELEVATION_ACCURACY_CD", 
"Project1"."APPL_ID3" AS "APPL_ID2", 
"Project1"."EWM_ELEV_MEASURE_METHOD_TYP_ID1" AS "EWM_ELEV_MEASURE_METHOD_TYP_ID1", 
"Project1"."EWM_ELEV_MEASURE_METHOD_DESC" AS "EWM_ELEV_MEASURE_METHOD_DESC", 
"Project1"."EWM_ELEV_MEASURE_METHOD_ACTV" AS "EWM_ELEV_MEASURE_METHOD_ACTV", 
"Project1"."EWM_ELEV_MEASURE_METHOD_ORDER" AS "EWM_ELEV_MEASURE_METHOD_ORDER", 
"Project1"."MODIFIED_DATE2" AS "MODIFIED_DATE3", 
"Project1"."MODIFIED_USER2" AS "MODIFIED_USER3", 
"Project1"."MODIFIED_PROC2" AS "MODIFIED_PROC3", 
"Project1"."EWM_ELEV_MEASURE_METHOD_CD" AS "EWM_ELEV_MEASURE_METHOD_CD", 
"Project1"."APPL_ID2" AS "APPL_ID3", 
"Project1"."ORG_ID1" AS "ORG_ID1", 
"Project1"."ORG_NAME" AS "ORG_NAME", 
"Project1"."ORG_ABBR" AS "ORG_ABBR", 
"Project1"."ORG_TYPE_ID" AS "ORG_TYPE_ID", 
"Project1"."MODIFIED_DATE4" AS "MODIFIED_DATE4", 
"Project1"."MODIFIED_USER4" AS "MODIFIED_USER4", 
"Project1"."MODIFIED_PROC4" AS "MODIFIED_PROC4", 
"Project1"."ORG_TIN" AS "ORG_TIN"
FROM ( SELECT 
"Extent1"."EWM_ELEVATION_DATA_READ_JRL_ID" AS "EWM_ELEVATION_DATA_READ_JRL_ID", 
"Extent1"."EWM_ELEVATION_DATA_READING_ID" AS "EWM_ELEVATION_DATA_READING_ID", 
"Extent1"."CRUD_TYPE" AS "CRUD_TYPE", 
"Extent1"."CRUD_DT" AS "CRUD_DT", 
"Extent1"."MEASUREMENT_DT" AS "MEASUREMENT_DT", 
"Extent1"."ORG_ID" AS "ORG_ID", 
"Extent1"."EWM_MEASUREMENT_ISSUE_TYPE_ID" AS "EWM_MEASUREMENT_ISSUE_TYPE_ID", 
"Extent1"."EWM_STATION_ID" AS "EWM_STATION_ID", 
"Extent1"."EWM_ELEV_MEASURE_METHOD_TYP_ID" AS "EWM_ELEV_MEASURE_METHOD_TYP_ID", 
"Extent1"."EWM_ELEVATION_ACCURACY_TYPE_ID" AS "EWM_ELEVATION_ACCURACY_TYPE_ID", 
"Extent1"."REFERENCE_POINT_ELEVATION" AS "REFERENCE_POINT_ELEVATION", 
"Extent1"."GROUND_SURFACE_ELEVATION" AS "GROUND_SURFACE_ELEVATION", 
"Extent1"."WATER_SURFACE_READING" AS "WATER_SURFACE_READING", 
"Extent1"."REFERENCE_POINT_READING" AS "REFERENCE_POINT_READING", 
"Extent1"."MANDATORY_READING" AS "MANDATORY_READING", 
"Extent1"."COMMENTS" AS "COMMENTS", 
"Extent1"."MODIFIED_DATE" AS "MODIFIED_DATE", 
"Extent1"."MODIFIED_USER" AS "MODIFIED_USER", 
"Extent1"."MODIFIED_PROC" AS "MODIFIED_PROC", 
"Extent1"."COOPERATING_AGENCY_ORG_ID" AS "COOPERATING_AGENCY_ORG_ID", 
"Extent1"."APPL_ID" AS "APPL_ID", 
"Extent1"."SUBMISSION_DATE" AS "SUBMISSION_DATE", 
1 AS "C1", 
"Extent2"."EWM_MEASUREMENT_ISSUE_TYPE_ID" AS "EWM_MEASUREMENT_ISSUE_TYPE_ID1", 
"Extent2"."EWM_MEASURE_ISSUE_TYPE_CODE" AS "EWM_MEASURE_ISSUE_TYPE_CODE", 
"Extent2"."EWM_MEASURE_ISSUE_TYPE_DESC" AS "EWM_MEASURE_ISSUE_TYPE_DESC", 
"Extent2"."EWM_MEASURE_ISSUE_TYPE_ACTV" AS "EWM_MEASURE_ISSUE_TYPE_ACTV", 
"Extent2"."EWM_MEASURE_ISSUE_TYPE_ORDER" AS "EWM_MEASURE_ISSUE_TYPE_ORDER", 
"Extent2"."EWM_MEASURE_ISSUE_TYPE_CLASS" AS "EWM_MEASURE_ISSUE_TYPE_CLASS", 
"Extent2"."MODIFIED_DATE" AS "MODIFIED_DATE1", 
"Extent2"."MODIFIED_USER" AS "MODIFIED_USER1", 
"Extent2"."MODIFIED_PROC" AS "MODIFIED_PROC1", 
"Extent2"."APPL_ID" AS "APPL_ID1", 
"Extent3"."EWM_ELEV_MEASURE_METHOD_TYP_ID" AS "EWM_ELEV_MEASURE_METHOD_TYP_ID1", 
"Extent3"."EWM_ELEV_MEASURE_METHOD_DESC" AS "EWM_ELEV_MEASURE_METHOD_DESC", 
"Extent3"."EWM_ELEV_MEASURE_METHOD_ACTV" AS "EWM_ELEV_MEASURE_METHOD_ACTV", 
"Extent3"."EWM_ELEV_MEASURE_METHOD_ORDER" AS "EWM_ELEV_MEASURE_METHOD_ORDER", 
"Extent3"."MODIFIED_DATE" AS "MODIFIED_DATE2", 
"Extent3"."MODIFIED_USER" AS "MODIFIED_USER2", 
"Extent3"."MODIFIED_PROC" AS "MODIFIED_PROC2", 
"Extent3"."EWM_ELEV_MEASURE_METHOD_CD" AS "EWM_ELEV_MEASURE_METHOD_CD", 
"Extent3"."APPL_ID" AS "APPL_ID2", 
**"Extent4"."EWM_ELEVATION_ACCURACY_TYPE_ID" AS "EWM_ELEVATION_ACCURACY_TYPE_ID1"**, 
"Extent4"."EWM_ELEVATION_ACCURACY_DESC" AS "EWM_ELEVATION_ACCURACY_DESC", 
"Extent4"."EWM_ELEVATION_ACCURACY_ACTV" AS "EWM_ELEVATION_ACCURACY_ACTV", 
"Extent4"."EWM_ELEVATION_ACCURACY_ORDER" AS "EWM_ELEVATION_ACCURACY_ORDER", 
"Extent4"."MODIFIED_DATE" AS "MODIFIED_DATE3", 
"Extent4"."MODIFIED_USER" AS "MODIFIED_USER3", 
"Extent4"."MODIFIED_PROC" AS "MODIFIED_PROC3", 
"Extent4"."EWM_ELEVATION_ACCURACY_CD" AS "EWM_ELEVATION_ACCURACY_CD", 
"Extent4"."APPL_ID" AS "APPL_ID3", 
"Extent5"."ORG_ID" AS "ORG_ID1", 
"Extent5"."ORG_NAME" AS "ORG_NAME", 
"Extent5"."ORG_ABBR" AS "ORG_ABBR", 
"Extent5"."ORG_TYPE_ID" AS "ORG_TYPE_ID", 
"Extent5"."MODIFIED_DATE" AS "MODIFIED_DATE4", 
"Extent5"."MODIFIED_USER" AS "MODIFIED_USER4", 
"Extent5"."MODIFIED_PROC" AS "MODIFIED_PROC4", 
"Extent5"."ORG_TIN" AS "ORG_TIN"
FROM     "EWM_ADM"."EWM_ELEVATION_DATA_READING_JRL" "Extent1"
LEFT OUTER JOIN "EWM_ADM"."EWM_MEASUREMENT_ISSUE_TYPE" "Extent2" ON "Extent1"."EWM_MEASUREMENT_ISSUE_TYPE_ID" = "Extent2"."EWM_MEASUREMENT_ISSUE_TYPE_ID"
LEFT OUTER JOIN "EWM_ADM"."EWM_ELEV_MEASURE_METHOD_TYP" "Extent3" ON "Extent1"."EWM_ELEV_MEASURE_METHOD_TYP_ID" = "Extent3"."EWM_ELEV_MEASURE_METHOD_TYP_ID"
LEFT OUTER JOIN "EWM_ADM"."EWM_ELEVATION_ACCURACY_TYPE" "Extent4" ON "Extent1"."EWM_ELEVATION_ACCURACY_TYPE_ID" = "Extent4"."EWM_ELEVATION_ACCURACY_TYPE_ID"
LEFT OUTER JOIN "BUS_ADM"."ORGANIZATION" "Extent5" ON "Extent1"."COOPERATING_AGENCY_ORG_ID" = "Extent5"."ORG_ID"
WHERE (("Extent1"."EWM_STATION_ID" = 52370) OR (("Extent1"."EWM_STATION_ID" IS NULL) AND (52370 IS NULL)))
)  "Project1"
ORDER BY "Project1"."EWM_ELEVATION_DATA_READ_JRL_ID" DESC

有问题的行是这一行（在查询中用星号标记，因为我不能将其加粗）。

"Extent4"."EWM_ELEVATION_ACCURACY_TYPE_ID" AS "EWM_ELEVATION_ACCURACY_TYPE_ID1"

正如您所看到的，它在别名后面附加了一个1。我需要防止这种情况或强制使用自定义别名。

linq

1条答案

按热度按时间
iaqfqrcu

iaqfqrcu1#

我知道这是一个老问题，但对于必须使用旧EF和Oracle 12c的人来说，这可能是实际的。但这里有一个对我来说很好的解决方案：
1.使用以下代码创建一个新的Interceptor：

public class InterceptorFixAliasesLength : IDbCommandInterceptor
    {
        public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            FixNameLength(command);
        }

        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        }

        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            FixNameLength(command);
        }

        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        private static void FixNameLength(DbCommand command)
        {
            var text = command.CommandText;
            // Looking something like "AS "PARAMETER_1"" where PARAMETER_1 is the parameter name and length is greater than 30 characters
            var pattern = @"AS ""([A-Z0-9_]{31,})""";
            var matches = Regex.Matches(text, pattern);

            var anyUpdated = false;
            foreach(Match m in matches)
            {
                // if the alias is longer than 30 characters, generate a new unique alias
                var paramName = m.Groups[1].Value;
                if (paramName.Length > 30)
                {
                    var newParamName = paramName.Substring(0, 27);
                    var newParamNameUnique = newParamName;
                    var i = 1;
                    while (text.Contains(newParamNameUnique))
                    {
                        newParamNameUnique = $"{newParamName}_{i}";
                        i++;
                    }
                    text = text.Replace($"\"{paramName}\"", $"\"{newParamNameUnique}\"");
                    anyUpdated = true;
                }
            }

            if (anyUpdated)
            {
                command.CommandText = text;
            }
        }
    }

1.将此拦截器添加到您的DbConfiguration：

public class MyDbConfiguration : DbConfiguration
    {
        public MyDbConfiguration()
        {
            AddInterceptor(new InterceptorFixAliasesLength());
        }
    }

1.现在所有的命令（只针对select请求）都将在执行前被检查和修复

赞(0）分享  回复(0）举报  2023-09-28
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com