There is an EF6 Code-First. When the Property entity is saved, spaces are removed at the end and / or beginning of the value of the Name property. The problem is that I need to keep these spaces, because This behavior does not allow to save entities differing in their spelling. Anticipating criticism that the preservation of such data is a mistake, I will explain that the program is designed specifically for analyzing and collecting such data.

How can I change the behavior of EntityFramework and allow whitespace to be stored at the end and / or beginning of the property value?

public partial class MyContext : DbContext { public MyContext() : base("name=MyContext") { } // ... protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Property>() .Property(e => e.Group) .IsFixedLength(); modelBuilder.Entity<Property>() .Property(e => e.Name) .IsFixedLength(); } } public partial class Property { public SEProperty() {} public int Id { get; set; } [Required] public string Group { get; set; } [StringLength(255)] public string Name { get; set; } }

The table creation script looks like this:

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Property]( [Id] [int] IDENTITY(1,1) NOT NULL, [Group] [nvarchar](50) NOT NULL, [Name] [nvarchar](255) NULL, CONSTRAINT [PK_dbo.Property] PRIMARY KEY CLUSTERED ( [Id] 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
  • Something you do not agree. With the Code-First approach from the classes shown, such SQL could not come out. - Alexander Petrov
  • If you remove IsFixedLength() , then the columns are created like nvarchar . Spaces are preserved and do not disappear anywhere. What is your project type - ASP.NET? I can assume that the data from the database are displayed in html. Accordingly, whitespace characters are cropped by the browser. - Alexander Petrov
  • @AlexanderPetrov I removed unimportant details. At the moment it looks like the problem is not in the EF, but in the SQL server. It returns the wrong entry. That is, when you select * from [Property] where [Name] = 'abc ' , trailing spaces are ignored and the entry with 'abc' returned. - XelaNimed

2 answers 2

The problem was not in EF, but in MS SQL server, which returned a string without trailing spaces, i.e. ignored trailing spaces when comparing strings, which in turn led to further errors. In other words, for the server, the strings 'abc' and 'abc ' equivalent.
It explains how the MS SQL server compares strings with spaces.

I solved the problem with a crutch - wrapping the values ​​into a predefined character, for example, '-abc -' . Thus, spaces can no longer be the first or last characters.

Besides the above, the problem occurs when using IsFixedLength() . Below are some examples of conversions to SQL, from which it follows that you should not use a fixed length for proper filtering:

 var prop = db.Properties.FirstOrDefault(e => e.Name.EndsWith("term")); // SQL: where [Name] LIKE '%term' var prop = db.Properties.FirstOrDefault(e => e.Name.StartsWith("term")); // SQL: where [Name] LIKE 'term%' var prop = db.Properties.FirstOrDefault(e => e.Name.Contains("term")); // SQL: where [Name] LIKE '%term%'
  • Well, well, that everything cleared up. So yes, ansi_padding is the place to be. - Alexander Petrov
  • @AlexanderPetrov Not sure that I correctly understood what you had in mind. The link above states that: SQL Server pads for it compares them. However, it is possible to set ANSI_PADDING for comparisons . Or am I looking in the wrong direction? - XelaNimed

Change the data type from nvarhcar to nchar to the desired length.

  • If I understand correctly, in this case, the remaining space will be filled with spaces, which will not give the opportunity to preserve the values ​​differing in the number of spaces at the end and / or beginning of the value. - XelaNimed