entity-framework-6 - 在 C# MVC 实体框架中创建和使用不在 EF 模型中的动态表

问题描述

环境是C#、MVC、Entity Framework、SQL Server。数据类型是固定的，但表名不是。创建它是为了在运行期间利用某些 SQL 功能。它的名称因 UserId 而异。

问题是，在创建之后，当我遍历我的循环，做我的工作时，我做一个查询，然后是一个插入。那是记录1。没问题。下一次迭代，即查询，设置为提取刚刚添加的内容，但由于实体框架错误而失败。

完成后，可能有数百名用户同时通过此代码，我真的不想动态更改模型路线。我害怕在生产中炸毁 EF 模型。我想让这个尽可能简单。“TempProspect”类在 EF 模型之外。这个临时表也是如此。EF是否有可能因为它在“活动”模式中而参与其中？同一个数据库？希望不是。

创作：

int abc = db.Database.ExecuteSqlCommand
        ("IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='TempProspectsUid" + GlobalConstants.CurrentUser.UserId + "' AND xtype='U')" +
            "CREATE TABLE [Campaigns].[TempProspectsUid" + GlobalConstants.CurrentUser.UserId + "]" +
            "(" +
                "[Id] [int] IDENTITY(1,1) NOT NULL," +
                "[IdxMatchingFirstLastNames1] [varchar] (128) NULL," +
                "[IdxMatchingFirstLastNames2] [varchar] (128) NULL," +
                "[IdxMatchingStreet1] [varchar] (64) NULL," +
                "[IdxMatchingCompanyName1] [varchar] (48) NULL," +
                "[IdxMatchingCompanyName2] [varchar] (48) NULL," +
                "[ProspectGroupId] [int] NULL," +
                "[CompanyId] [int] NULL," +
                "[CompanyName] [nvarchar](48) NULL," +
                "[Department] [nvarchar](48) NULL," +
                "[Division] [nvarchar](48) NULL," +
                "[Position] [nvarchar](48) NULL," +
                "[Gender] [char](1) NULL," +
                "[DateOfBirth] [varchar](10) NULL," +
                "[Title] [varchar](10) NULL," +
                "[FirstName] [nvarchar](64) NULL," +
                "[MiddleName1] [nvarchar](32) NULL," +
                "[MiddleName2] [nvarchar](32) NULL," +
                "[LastName] [nvarchar](64) NULL," +
                "[NameSuffix] [nvarchar](16) NULL," +
                "[PhoneHome] [varchar](24) NULL," +
                "[PhoneMobile] [varchar](24) NULL," +
                "[PhoneWork] [varchar](24) NULL," +
                "[MobileProvider] [smallint] NULL," +
                "[EmailPersonal] [varchar](64) NULL," +
                "[EmailWork] [varchar](64) NULL," +
                "[Language] [smallint] NULL," +
                "[Street1] [varchar](64) NULL," +
                "[Street2] [varchar](64) NULL," +
                "[PobGeneral] [varchar](64) NULL," +
                "[SpecialDelivery] [varchar](64) NULL," +
                "[City] [varchar](64) NULL," +
                "[StateOrProvince] [varchar](64) NULL," +
                "[ZipOrPostalCode] [varchar](16) NULL," +
                "[CountryCode] [int] NOT NULL," +
                "CONSTRAINT [PK_TempProspectsUid" + GlobalConstants.CurrentUser.UserId + "] " +
                    "PRIMARY KEY CLUSTERED( [Id] ASC ), " +
                "INDEX Idx_MatchingFirstLastNames1 NONCLUSTERED(IdxMatchingFirstLastNames1), " +
                "INDEX Idx_MatchingFirstLastNames2 NONCLUSTERED(IdxMatchingFirstLastNames2), " +
                "INDEX Idx_MatchingStreet1 NONCLUSTERED(IdxMatchingStreet1), " +
                "INDEX Idx_MatchingCompanyName1 NONCLUSTERED(IdxMatchingCompanyName1), " +
                "INDEX Idx_MatchingCompanyName2 NONCLUSTERED(IdxMatchingCompanyName2)" +
                "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, " +
                    "ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) " +
            "ON [PRIMARY]) " +
            "ON [PRIMARY]"
        );

QUERY：（我尝试过很多不同的语法，包括 Object[] 和 SQLParameter 等，都失败，结果相同）

lstTempProspects = db.Database.SqlQuery ($"SELECT * FROM [Campaigns].[TempProspectsUid{GlobalConstants.CurrentUser.UserId}] WHERE IdxMatchingFirstLastNames2 = '{qm1}'").ToList();

插入：

int numberOfRowsInserted = db.Database.ExecuteSqlCommand
(@"INSERT INTO [Campaigns].[TempProspectsUid" + GlobalConstants.CurrentUser.UserId + "] " +
             "(IdxMatchingFirstLastNames1, IdxMatchingFirstLastNames2, IdxMatchingStreet1, " +
             "IdxMatchingCompanyName1, IdxMatchingCompanyName2, ProspectGroupId, CompanyId, " +
              "CompanyName, Department, Division, Position, Gender, DateOfBirth, " +
              "Title, FirstName, MiddleName1, MiddleName2, LastName, NameSuffix, " +
              "PhoneHome, PhoneMobile, PhoneWork, MobileProvider, EmailPersonal, " +
              "EmailWork, Language, Street1, Street2, PobGeneral, SpecialDelivery, " +
              "City, StateOrProvince, ZipOrPostalCode, CountryCode) " +
              "VALUES " +
             "('" + 
              tp.IdxMatchingFirstLastNames1 + "', '" + tp.IdxMatchingFirstLastNames2 + "', '" + 
              tp.IdxMatchingStreet1 + "', '" + tp.IdxMatchingCompanyName1 + "', '" +
              tp.IdxMatchingCompanyName2 + "', 0, 0, '" + tp.CompanyName + "', '" + 
              tp.Department + "', '" + tp.Division + "', '" +
              …….
              tp.Street1 + "', '" + tp.Street2 + "', '" + tp.PobGeneral + "', '" +
               tp.SpecialDelivery + "', '" + tp.City + "', '" + tp.StateOrProvince + "', '" +
               tp.ZipOrPostalCode + "', '" + tp.CountryCode + "')"

错误：

抛出异常：EntityFramework.dll 中的“System.InvalidOperationException”抛出异常：mscorlib.dll 中的“System.InvalidOperationException”

标签： entity-framework-6

entity-framework-6 - 在 C# MVC 实体框架中创建和使用不在 EF 模型中的动态表

问题描述

解决方案

推荐阅读