{"id":405866,"date":"2023-11-20T14:37:55","date_gmt":"2023-11-20T13:37:55","guid":{"rendered":"https:\/\/blog.jetbrains.com\/?post_type=dotnet&p=405866"},"modified":"2024-02-27T19:57:29","modified_gmt":"2024-02-27T18:57:29","slug":"visualize-entity-framework-relationships-and-additional-query-analysis-in-resharper-2023-3","status":"publish","type":"dotnet","link":"https:\/\/blog.jetbrains.com\/zh-hans\/dotnet\/2023\/11\/20\/visualize-entity-framework-relationships-and-additional-query-analysis-in-resharper-2023-3","title":{"rendered":"Visualize Entity Framework Relationships and Additional Query Analysis in ReSharper 2023.3"},"content":{"rendered":"\n

A lot of teams are using Entity Framework or EF Core to work with their database. As an Object-Relational Mapper (ORM), it bridges objects in code to a relational database model, so that as a developer you don\u2019t have to worry too much about the actual database.<\/p>\n\n\n\n

We all know: that\u2019s not entirely true. Even with Entity Framework and EF Core, you still have to add indexes<\/a> so data can be retrieved quickly, be wary about the N+1 problem<\/a>, and many other potential pitfalls.<\/p>\n\n\n\n

The latest ReSharper 2023.3 comes with a number of tools and inspections to help you catch potential issues with Entity Framework and EF Core, including a way to generate an Entity Relationship Diagram (ERD) for your database context. Let\u2019s have a look.<\/p>\n\n\n

\n
\n Download ReSharper and try it out!<\/a>\n <\/div>\n <\/div>\n\n\n\n\n\n\n\n

Entity Relationship Diagram<\/h2>\n\n\n\n

The most visual update in ReSharper 2023.3 is the new Entity Relationship Diagram (ERD) that you can use to inspect how entities are related to each other. When you open any entity that is part of an Entity Framework or EF Core database context in the editor, you will see that ReSharper identifies it as such:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

When you press Alt+Enter<\/strong>, you can then use the Show Entity Relationship Diagram<\/strong> action to generate an ERD for the current database context. Clicking the database icon also works, and there\u2019s the ReSharper | Architecture | Show Entity Relationship Diagram<\/strong> menu as well.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

ReSharper generates a graphic representation of the database model and the relationships between entities, including one-to-one, one-to-many and many-to-many relationships. You can pan and zoom, and look at the various entities in your database context.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The diagram also highlights string properties with unlimited length. A perfect segue to the next section!<\/p>\n\n\n\n

Unlimited string length inspection<\/h2>\n\n\n\n

One of the new inspections introduced in ReSharper 2023.3 analyzes your database context and its entities for properties that have unlimited string length, and highlights them:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To fix this inspection, you can add a [StringLength]<\/code><\/a> or [MaxLength]<\/code><\/a> attribute to specify the length of the string in the database model. Alternatively, you can use the HasMaxLength()<\/code> builder method in your database context model creation logic:<\/p>\n\n\n\n

public class Database : DbContext\n{\n    \/\/ ...\n\n    protected override void OnModelCreating(ModelBuilder builder)\n    {\n        \/\/ Country\n        builder.Entity<Country>().Property(ci => ci.Name)\n            .HasMaxLength(100);\n    }\n}\n<\/pre>\n\n\n\n
In SQL Server and Azure SQL Database, an unbounded string with unlimited length will be stored in a nvarchar(max)<\/code> column. When you know the data will never exceed a certain length, limiting the string length in your EF model will translate into the data being stored in an nvarchar(N)<\/code> column.<\/p>\n\n\n\n
Now, why does this matter? By default, SQL Server stores data in pages on disk<\/a> where each row and its data can be 8060 bytes in size. Large object data types (LOB) such as xml<\/code>, varbinary(max)<\/code>, and varchar(max)<\/code>, are stored in a separate location in SQL Server. When a row is too large (or has many LOBs), SQL Server can not read data in one go and has to look in several places to filter and return the result set for your query, resulting in additional I\/O overhead.<\/p>\n\n\n\n
In MySQL and MariaDB, row size is constrained to 65535 bytes<\/a>, so limiting how long a row can get is crucial to keeping your database and its performance happy!<\/p>\n\n\n\n
The new unlimited string length<\/em> inspection helps you discover properties that have the potential to impact database performance<\/strong>.<\/p>\n\n\n\n
DbFunction inspections<\/h2>\n\n\n\n
Another new inspection helps detect usages of methods in Entity Framework-related code that could potentially produce runtime exceptions. Whenever you use a function that is not convertible to SQL, ReSharper will let you know about this.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Additionally, when using methods that are only to be used in a database context, such as functions in the EF.Functions<\/code> property, ReSharper informs you that the database function must not be called in non-database context<\/strong>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
These 2 new inspections will make sure that potential exceptions in executing (non-)database queries are discovered even before running your application.<\/p>\n\n\n\n
In conclusion<\/h2>\n\n\n\n
Being able to inspect the relationships between entities in an Entity Relationship Diagram (ERD) is a great opportunity for finding missing relationships or dependency loops in a graphical way. <\/p>\n\n\n\n
Combined with the ability to find properties that may negatively impact query performance, and to find usages of functions that do not belong in an Entity Framework query, this ReSharper release will help you build more solid applications using Entity Framework and EF Core. <\/p>\n\n\n\n
And of course, don\u2019t forget the Entity Framework and EF Core N+1 inspections<\/a> introduced in the previous ReSharper and JetBrains Rider releases!<\/p>\n\n\n\n
What do you think of the new ERD and inspections? Let us know in the comments!<\/p>\n","protected":false},"author":118,"featured_media":405933,"comment_status":"closed","ping_status":"closed","template":"","categories":[4992,1401],"tags":[118,6927,46,1978],"cross-post-tag":[],"acf":[],"_links":{"self":[{"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/dotnet\/405866"}],"collection":[{"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/dotnet"}],"about":[{"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/types\/dotnet"}],"author":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/users\/118"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/comments?post=405866"}],"version-history":[{"count":10,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/dotnet\/405866\/revisions"}],"predecessor-version":[{"id":449341,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/dotnet\/405866\/revisions\/449341"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/media\/405933"}],"wp:attachment":[{"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/media?parent=405866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/categories?post=405866"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/tags?post=405866"},{"taxonomy":"cross-post-tag","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/zh-hans\/wp-json\/wp\/v2\/cross-post-tag?post=405866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}