Entity Framework and Rule Names
Remember, Ursula Le Guin in The Wizard of Earthsea: “Never ask a person about his name. Never call yours. ” Unfortunately, the Entity Framework is out of the box absolutely not guided by this remarkable rule, and when generating classes based on the database schema (Database First strategy) it names the classes, class properties and navigation properties exactly as the corresponding table in the database is named.
And what to do if there is a task to develop a new project to an already existing database, in which the tables are named after a certain pattern, say, according to the t_tablename pattern (for example, t_order_product). A completely different naming convention has been adopted in the project, and developers want to see “human,” from their point of view, names (OrderProduct). Of course, you can get out of the situation by taking the appropriate administrative decision, but sometimes you really don't want to go against the sense of beauty.
In addition, code generation templates appeared at the Entity Framework. It would seem: for this they appeared, so that the developer could manage the process of creating classes based on the database schema. However, it was not possible to confine with templates alone, but more on that below.
So, the challenge: to achieve automatic renaming of entities when creating / updating the data model. For simplicity, we assume that the column names are in an acceptable format, and we will only use table names.
')
The table name spawns the following names:
The T4 template itself generates only the file names, and it draws everything else from the Model.edmx file. This file contains a description of database entities, a conceptual model, and the mapping of one to another. Theoretically, you can force the template to generate the changed names of the entities, but this will lead to sad consequences. Since the new name will not be matched in the .edmx file, any query to the database will fail — the QueryProvider component of the SQL query simply does not detect the table name by the class name. To verify this, it is enough to automatically refactor try renaming the name of some class. A simple conclusion follows from this - it is required to modify the .edmx file itself.
In fact, this task is not so much difficult as a dreary. Knowledge of the .edmx structure and understanding of what is appropriate there is necessary. In addition, in order for everything to be at its best, it will be necessary in some cases to translate the name into the plural. With DbContext, everything is simple: take and translate. With navigation properties it is more difficult: it is necessary to analyze the connections and determine which side this connection “comes in”: one or many. In addition to the .edmx file, you also need to modify the .edmx.diagram file - this is where the description of the beautiful tablets that can be seen in the model designer is stored.
The result of solving the problem described above was the class EntityTransformer, which I want to bring to your attention. All it does is load the .edmx file, analyze its contents, change the values of the corresponding attributes, and save the modified file. If you want to use it for your own purposes, all you need to do is modify the
You can start the conversion in several ways - for example, create an external console application. However, since I wanted to get as much as possible a “seamless” solution and still use the templates (if there are any), the class was decorated in a template and connected to the main code generation template. The connection itself is trivial, just add the EntityTransformer.ttinclude file to the project, copy the template below into it and add the selected lines to the Model.tt file:
In addition, you will need to add a reference to the
To run the templates, they will have to be opened and saved, and this will have to be done twice: for Model.tt and for Model.Context.tt. Otherwise, either the DbContext or the entity classes will retain the original names (table names). Unfortunately, I could not find a way to automatically launch templates.
To test the pattern, we used a database that emulates the most typical situations: one-to-many, one-to-one, many-to-many relationships, several relationships between two tables.
Successful to you renaming!
And what to do if there is a task to develop a new project to an already existing database, in which the tables are named after a certain pattern, say, according to the t_tablename pattern (for example, t_order_product). A completely different naming convention has been adopted in the project, and developers want to see “human,” from their point of view, names (OrderProduct). Of course, you can get out of the situation by taking the appropriate administrative decision, but sometimes you really don't want to go against the sense of beauty.
In addition, code generation templates appeared at the Entity Framework. It would seem: for this they appeared, so that the developer could manage the process of creating classes based on the database schema. However, it was not possible to confine with templates alone, but more on that below.
So, the challenge: to achieve automatic renaming of entities when creating / updating the data model. For simplicity, we assume that the column names are in an acceptable format, and we will only use table names.
')
The table name spawns the following names:
- Actually class name (Entity)
- The name of the file where the class is stored (Entity.cs)
- Property name for accessing multiple classes from a database context (DbContext.Entities)
- Names of navigation properties
- The name of the figures in the visual designer
The T4 template itself generates only the file names, and it draws everything else from the Model.edmx file. This file contains a description of database entities, a conceptual model, and the mapping of one to another. Theoretically, you can force the template to generate the changed names of the entities, but this will lead to sad consequences. Since the new name will not be matched in the .edmx file, any query to the database will fail — the QueryProvider component of the SQL query simply does not detect the table name by the class name. To verify this, it is enough to automatically refactor try renaming the name of some class. A simple conclusion follows from this - it is required to modify the .edmx file itself.
In fact, this task is not so much difficult as a dreary. Knowledge of the .edmx structure and understanding of what is appropriate there is necessary. In addition, in order for everything to be at its best, it will be necessary in some cases to translate the name into the plural. With DbContext, everything is simple: take and translate. With navigation properties it is more difficult: it is necessary to analyze the connections and determine which side this connection “comes in”: one or many. In addition to the .edmx file, you also need to modify the .edmx.diagram file - this is where the description of the beautiful tablets that can be seen in the model designer is stored.
The result of solving the problem described above was the class EntityTransformer, which I want to bring to your attention. All it does is load the .edmx file, analyze its contents, change the values of the corresponding attributes, and save the modified file. If you want to use it for your own purposes, all you need to do is modify the
Transform(string inputString, bool pluralize) method Transform(string inputString, bool pluralize) , which defines the rules for renaming entities.You can start the conversion in several ways - for example, create an external console application. However, since I wanted to get as much as possible a “seamless” solution and still use the templates (if there are any), the class was decorated in a template and connected to the main code generation template. The connection itself is trivial, just add the EntityTransformer.ttinclude file to the project, copy the template below into it and add the selected lines to the Model.tt file:
In addition, you will need to add a reference to the
System.Data.Entity.Design in the project - this is necessary for using PluralizationService . If you do not need to convert to the plural, then you can not add the link, and exclude the corresponding lines of code from the template.To run the templates, they will have to be opened and saved, and this will have to be done twice: for Model.tt and for Model.Context.tt. Otherwise, either the DbContext or the entity classes will retain the original names (table names). Unfortunately, I could not find a way to automatically launch templates.
EntityTransformer.ttinclude template
<#@ assembly name="System.Data.Entity.Design" #> <#@ import namespace="System" #> <#@ import namespace="System.Data.Entity.Design.PluralizationServices" #> <#@ import namespace="System.Globalization" #> <#@ import namespace="System.Linq" #> <#@ import namespace="System.Text.RegularExpressions" #> <#@ import namespace="System.Xml.Linq" #> <#+ public class EntityTransformer { readonly PluralizationService _pluralizationService = PluralizationService.CreateService(CultureInfo.GetCultureInfo("en-US")); const string DESIGNER_NAMESPACE = "http://schemas.microsoft.com/ado/2009/11/edmx"; private string Transform(string inputString, bool pluralize) { string result = string.Empty; const string PREFIX = "t_"; Regex regex = new Regex(string.Format(@"(?<namespace>\w+?\.)*(?<prefix>{0})*(\w+)", PREFIX)); var groups = regex.Match(inputString).Groups; string namespc = groups["namespace"].Value; string[] parts = groups[1].Value.Split(new[] {"_"}, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < parts.Length; i++) { string addingPart = FirstCharToUpper(parts[i]); if (pluralize && i == parts.Length - 1) addingPart = _pluralizationService.Pluralize(addingPart); result += addingPart; } result = namespc + result; return result; } private string Transform(string inputString) { string result = Transform(inputString, false); return result; } private void Transform(XAttribute attribute) { attribute.Value = Transform(attribute.Value); } private void Transform(XAttribute attribute, bool pluralize) { attribute.Value = Transform(attribute.Value, pluralize); } public void TransformEntities(string inputFile) { XDocument document = XDocument.Load(inputFile); const string SSDL_NAMESPACE = "http://schemas.microsoft.com/ado/2009/11/edm/ssdl"; const string CSDL_NAMESPACE = "http://schemas.microsoft.com/ado/2009/11/edm"; const string MSL_NAMESPACE = "http://schemas.microsoft.com/ado/2009/11/mapping/cs"; XElement ssdl = document.Descendants(XName.Get("Schema", SSDL_NAMESPACE)).First(); XElement csdl = document.Descendants(XName.Get("Schema", CSDL_NAMESPACE)).First(); XElement msl = document.Descendants(XName.Get("Mapping", MSL_NAMESPACE)).First(); XElement designerDiagram = document.Descendants(XName.Get("Designer", DESIGNER_NAMESPACE)).First(); TransformCsdl(csdl, ssdl); TransformMsl(MSL_NAMESPACE, msl); TransformDesigner(DESIGNER_NAMESPACE, designerDiagram, inputFile); document.Save(inputFile); } private void TransformDesigner(string designerNamespace, XElement designerDiagram, string modelFilePath) { Action<XElement> transformDesigner = diagram => { var shapes = diagram.Descendants(XName.Get("EntityTypeShape", designerNamespace)); foreach (var item in shapes) Transform(item.Attribute("EntityType")); }; transformDesigner(designerDiagram); string diagramFilePath = string.Format("{0}.diagram", modelFilePath); if (File.Exists(diagramFilePath)) { XDocument document = XDocument.Load(diagramFilePath); designerDiagram = document.Descendants(XName.Get("Designer", DESIGNER_NAMESPACE)).First(); transformDesigner(designerDiagram); document.Save(diagramFilePath); } } private void TransformMsl(string mslNamespace, XElement msl) { var entityContainerMapping = msl.Element(XName.Get("EntityContainerMapping", mslNamespace)); if (entityContainerMapping == null) throw new Exception("Element EntityContainerMapping not found."); foreach (var entitySetMapping in entityContainerMapping.Elements(XName.Get("EntitySetMapping", mslNamespace))) { Transform(entitySetMapping.Attribute("Name"), true); foreach (var entityTypeMapping in entitySetMapping.Elements(XName.Get("EntityTypeMapping", mslNamespace))) Transform(entityTypeMapping.Attribute("TypeName")); } } private void TransformCsdl(XElement csdl, XElement ssdl) { string csdlNamespace = csdl.GetDefaultNamespace().NamespaceName; Func<XElement, string, IEnumerable<XElement>> getElements = (root, localName) => root.Elements(XName.Get(localName, csdlNamespace)); var entityContainer = csdl.Element(XName.Get("EntityContainer", csdlNamespace)); if (entityContainer == null) throw new Exception("Element EntityContainer not found."); foreach (var entitySet in getElements(entityContainer, "EntitySet")) { Transform(entitySet.Attribute("Name"), true); Transform(entitySet.Attribute("EntityType")); } foreach (var associationSet in getElements(entityContainer, "AssociationSet")) foreach (var end in getElements(associationSet, "End")) Transform(end.Attribute("EntitySet"), true); foreach (var entityType in getElements(csdl, "EntityType")) Transform(entityType.Attribute("Name")); foreach (var association in getElements(csdl, "Association")) foreach (var end in getElements(association, "End")) Transform(end.Attribute("Type")); TransformNavigationProperties(csdl, ssdl); } private void TransformNavigationProperties(XElement csdl, XElement ssdl) { string ssdlNamespace = ssdl.GetDefaultNamespace().NamespaceName; string csdlNamespace = csdl.GetDefaultNamespace().NamespaceName; var associationSets = ssdl.Descendants(XName.Get("AssociationSet", ssdlNamespace)); foreach (XElement associationSet in associationSets) { var association = ssdl.Descendants(XName.Get("Association", ssdlNamespace)) .Single(a => a.Attribute("Name").Value == associationSet.Attribute("Name").Value); var roles = association.Elements().Where(e => e.Name.LocalName == "End"); var manyRole = roles.FirstOrDefault(e => e.Attribute("Multiplicity").Value == "*"); var csdlAssotiationSet = csdl.Descendants(XName.Get("AssociationSet", csdlNamespace)) .Single(e => e.Attribute("Name").Value == associationSet.Attribute("Name").Value); string associationName = csdlAssotiationSet.Attribute("Association").Value; var navigationProperties = csdl.Descendants(XName.Get("NavigationProperty", csdlNamespace)) .Where(e => e.Attribute("Relationship").Value == associationName); foreach (XElement navigationProperty in navigationProperties) { bool pluralize = manyRole != null && navigationProperty.Attribute("ToRole").Value == manyRole.Attribute("Role").Value; Transform(navigationProperty.Attribute("Name"), pluralize); } } } private static string FirstCharToUpper(string input) { if (String.IsNullOrEmpty(input)) throw new ArgumentException("Empty string"); return input.First().ToString().ToUpper() + input.Substring(1); } } #> To test the pattern, we used a database that emulates the most typical situations: one-to-many, one-to-one, many-to-many relationships, several relationships between two tables.
Script to create test tables
CREATE TABLE [dbo].[t_address]( [AddressId] [int] IDENTITY(1,1) NOT NULL, [AddressName] [nvarchar](500) NOT NULL, CONSTRAINT [PK_t_address] PRIMARY KEY CLUSTERED ( [AddressId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_customer]( [CustomerId] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [nvarchar](50) NOT NULL, [LocationAddressId] [int] NULL, [PostalAddressId] [int] NULL, PRIMARY KEY CLUSTERED ( [CustomerId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_customer_info]( [CustomerId] [int] NOT NULL, [CustomerDescription] [nvarchar](50) NULL, CONSTRAINT [PK_t_customer_info] PRIMARY KEY CLUSTERED ( [CustomerId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_order]( [OrderId] [int] IDENTITY(1,1) NOT NULL, [CustomerId] [int] NOT NULL, [CreateDate] AS (getdate()), CONSTRAINT [PK__t_Order__C3905BCFC0AF501C] PRIMARY KEY CLUSTERED ( [OrderId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_order_product]( [OrderId] [int] NOT NULL, [ProductId] [int] NOT NULL, [Count] [int] NOT NULL, CONSTRAINT [PK_t_order_product] PRIMARY KEY CLUSTERED ( [OrderId] ASC, [ProductId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_product]( [ProductId] [int] IDENTITY(1,1) NOT NULL, [ProductName] [nvarchar](100) NOT NULL, [ProductPrice] [decimal](10, 2) NOT NULL, CONSTRAINT [PK_t_product] PRIMARY KEY CLUSTERED ( [ProductId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[t_test_person]( [TestId] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_t_test_person] PRIMARY KEY CLUSTERED ( [TestId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[t_customer] WITH CHECK ADD CONSTRAINT [FK_t_customer_t_address] FOREIGN KEY([LocationAddressId]) REFERENCES [dbo].[t_address] ([AddressId]) GO ALTER TABLE [dbo].[t_customer] CHECK CONSTRAINT [FK_t_customer_t_address] GO ALTER TABLE [dbo].[t_customer] WITH CHECK ADD CONSTRAINT [FK_t_customer_t_address1] FOREIGN KEY([PostalAddressId]) REFERENCES [dbo].[t_address] ([AddressId]) GO ALTER TABLE [dbo].[t_customer] CHECK CONSTRAINT [FK_t_customer_t_address1] GO ALTER TABLE [dbo].[t_customer_info] WITH CHECK ADD CONSTRAINT [FK_t_customer_info_t_customer] FOREIGN KEY([CustomerId]) REFERENCES [dbo].[t_customer] ([CustomerId]) GO ALTER TABLE [dbo].[t_customer_info] CHECK CONSTRAINT [FK_t_customer_info_t_customer] GO ALTER TABLE [dbo].[t_order] WITH CHECK ADD CONSTRAINT [FK_t_Order_To_t_Customer] FOREIGN KEY([CustomerId]) REFERENCES [dbo].[t_customer] ([CustomerId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[t_order] CHECK CONSTRAINT [FK_t_Order_To_t_Customer] GO ALTER TABLE [dbo].[t_order_product] WITH CHECK ADD CONSTRAINT [FK_t_order_product_t_order] FOREIGN KEY([OrderId]) REFERENCES [dbo].[t_order] ([OrderId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[t_order_product] CHECK CONSTRAINT [FK_t_order_product_t_order] GO ALTER TABLE [dbo].[t_order_product] WITH CHECK ADD CONSTRAINT [FK_t_order_product_t_product] FOREIGN KEY([ProductId]) REFERENCES [dbo].[t_product] ([ProductId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[t_order_product] CHECK CONSTRAINT [FK_t_order_product_t_product] GO Successful to you renaming!
Source: https://habr.com/ru/post/236371/
All Articles