How to switch the storage model of Entity Framework

Recently, a project needs to support both SQL Server and SQLite. Although the Entity Framework still has many shortcomings, it basically meets the requirements of my project. In addition, I also hope to make an attempt. After all, although the use of past technologies and techniques may be more reliable, more skilled, and less risky, in the technology industry, you cannot stop. If a new technology has some features you need, if you think it is worth trying, you should try it. Although the attempt may fail (and you have to swallow the bitter fruit of failure), the benefits of success are also great, it also represents another step forward. I am also a newbie to Entity Framework. If you find any improvement or incorrect content in this article, please kindly advise.

 

No more nonsense. Start with the topic. Entity Framework requires three types of files: CSDL (conceptual Architecture Definition Language), SSDL (Storage Architecture Definition Language), MSL (ing standard language), and, A class file generated accordingly (this class file is not studied here ). In terms of this structure, EF fully supports the independence and separation between the upper-level stable conceptual architecture and the underlying changeable Storage Architecture. However, we usually use the vs designer to generate only one unique one. edmx files, including CSDL, SSDL, and MSL. Essentially, the three files can be completely separated into three files. For our current goal, we need to strip ssdl from the. edmx file and create the SSDL for SQL Server and SQLite.

 

The following describes my practices.

1. Create an SQLite database based on the table and view structure in SQL Server

Since SQL Server already has related tables and views, you can use SQL server as a template to create an SQLite database, in the SQLite database, create tables and views that are almost identical to those in the SQL server. The reason for saying "almost identical" is that the type system in SQLite is very different from that in SQL Server. However, because I have not used a special type in SQL Server, it is easier to solve this problem. I generate a table and View Script from SQL Server, and then modify the script so that SQLite can be recognized and executed. The main modifications include removing the "DBO" Architecture designation and changing the Declaration "int identity (1, 1) primary key" of the primary key of the auto-increment identity column to "integer primary key autoincrement ", the latter is the declaration statement for auto-incrementing primary keys in SQLite. For other simple types, such as varchar, nvarchar, bit, and money, the original declaration remains unchanged, these types are not supported by SQLite, but they are not error-prone during execution, and the ". schema "command to query the table schema, these types of declarations are retained (later when EDM for SQLite is generated, it seems that these types of declarations have a positive effect). In addition, one thing, this time I didn't use the datetime type, so I couldn't determine the difference in the support for the two databases. The Type System of SQLite is dynamic. Although you can specify a type when creating a table, you can insert a string into an "int" column. Finally, I didn't create any foreign key constraints in SQLite. Although SQLite supports foreign keys, it sacrifices some "integrity" to simplify the complexity (after all, this is my first attempt.

Since the tables and views in SQLite are exactly the same as those in SQL Server (except for the inherent differences in types), the two can be ensured when EDM is created, CSDL and MSL are identical.

2. Create an EDM instance based on SQL Server

The specific method is the same as what we usually do. Use the powerful vs designer to generate models from SQL Server and create associations between entities. After this step, we will get a. edmx file and a corresponding class file.

 

3. Create an SSDL file for SQL Server

Open the generated. edmx file in the XML editor, and you can clearly distinguish the SSDL, CSDL, and MSL parts. Copy all the content of the <schema> node under the "<edmx: storagemodels>" node, which is marked in yellow:

 

Create an XML file with the suffix. SSDL and paste the copied schema element. Save. You can.

 

3. Create an SSDL file for SQLite

There are two methods in this step:

• Copy the SSDL file for SQL Server and modify it to meet the SQLite requirements. It mainly removes some items not supported by SQLite, such as schema = "DBO. Modify the provider attribute and providermanifesttoken attribute of the schema element. The former needs to be changed to "system. Data. SQLite", and the latter needs to be changed to "iso8601" (Why are these two values changed? You will see it later)
• Use the vs designer to generate an edmx file for the SQLite database again, and then extract the SSDL definition.

I am using the second method. Here we need to use ADO. Net for SQLite to provideProgramThat is, system. Data. SQLite. This item can be found at the following link/

After the download is complete, you can install it. Then you can reference system. Data. SQLite in the project and use the designer to create edm for the SQLite database. The content for creating and extracting SSDL is exactly the same as that for SQL Server. Therefore, we can get an SSDL file for SQLite, where the provider attribute value of the schema element is system. data. SQLite, while the providermanifesttoken attribute is iso8601.

It should also be pointed out that, during the test, it was found that the operation failed because, in SSDL, the referenced and referenced fields (foreign key fields) of different table/object types are inconsistent, for example, if the type of the company-type companyid field in the company-type is integer, but the type of the companyid field in the employee type that references it is int, here, for example, only the two of them can be Int or integer.

4. Change the connection string to switch between different SSDL

In my project, I embedded the two SSDL into the Assembly as resources of two different sets respectively. Then, modify the connection string to switch to an SSDL. The connection string looks like this:

Metadata = Res: // Ge/gtdp. gtdedm. CSDL | res ://Litetdp/litetdp. gtdedm. SSDL| Res: // Ge/gtdp. gtdedm. MSL; provider = system. data. SQLite; provider connection string = & quot; Data Source = D: \ practice \ SQLite \ Ricky. DB; & quot;

Note that CSDL, SSDL, and MSL are specified in the connection string to the relevant resources of the specific assembly. The yellow background specifies the SSDL using SQLite, CSDL and MSL directly use the edmx content generated in step 2.

 

Supplement:

The edmx generated in step 2 is completely retained, so that you can still open and operate it at any time using the designer. Edmx generated for SQLite is deleted after SSDL is extracted. Of course, it's just a matter of deletion. It's totally needed, and, um, I'm in the mood. Haha.

If you haven't written anything for a long time, you won't be able to speak any more. If you are awkward to read, please forgive me.