如何指定属性应生成 TEXT 列而不是 nvarchar(4000)

发布于 2024-10-15 17:56:40 字数 1924 浏览 2 评论 0原文

我正在使用实体框架的代码优先功能,并试图弄清楚如何指定自动生成数据库时应创建的列数据类型。

我有一个简单的模型:

public class Article
{
    public int ArticleID { get; set; }

    public string Title { get; set; }
    public string Author { get; set; }
    public string Summary { get; set; }
    public string SummaryHtml { get; set; }
    public string Body { get; set; }
    public string BodyHtml { get; set; }
    public string Slug { get; set; }

    public DateTime Published { get; set; }
    public DateTime Updated { get; set; }

    public virtual ICollection<Comment> Comments { get; set; }
}

当我运行应用程序时,会使用以下架构自动创建 SQL CE 4.0 数据库:

DB Schema

到目前为止,一切都很好!但是,我将插入到 BodyBodyHtml 属性中的数据通常大于 NVarChar 列类型允许的最大长度,因此我希望 EF 为这些属性生成 Text 列。

但是,我似乎找不到办法做到这一点!经过相当多的谷歌搜索和阅读后,我尝试使用 DataAnnotations 指定列类型,从 这个答案

using System.ComponentModel.DataAnnotations;

...

[Column(TypeName = "Text")]
public string Body { get; set; }

这会引发以下异常(当删除数据库并重新运行应用程序时):

Schema specified is not valid. Errors: (12,6) : error 0040: The Type text is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification.

但我有不知道我应该指定什么名称空间或别名,而且我找不到任何可以告诉我的信息。

我还尝试按照 this 更改注释参考

using System.Data.Linq.Mapping;

...

[Column(DbType = "Text")]
public string Body { get; set; }

在这种情况下,创建了一个数据库,但是Body列仍然是一个NVarChar(4000),所以看起来该注释被忽略。

有人可以帮忙吗?这看起来应该是一个相当普遍的需求,但我的搜索却毫无结果!

I'm working with the Code First feature of Entity Framework and I'm trying to figure out how I can specify the column data types that should be created when the database is auto-generated.

I have a simple model:

public class Article
{
    public int ArticleID { get; set; }

    public string Title { get; set; }
    public string Author { get; set; }
    public string Summary { get; set; }
    public string SummaryHtml { get; set; }
    public string Body { get; set; }
    public string BodyHtml { get; set; }
    public string Slug { get; set; }

    public DateTime Published { get; set; }
    public DateTime Updated { get; set; }

    public virtual ICollection<Comment> Comments { get; set; }
}

When I run my application, a SQL CE 4.0 database is automatically created with the following schema:

DB Schema

So far, so good! However, the data I will be inserting into the Body and BodyHtml properties is routinely larger than the maximum allowed length for the NVarChar column type, so I want EF to generate Text columns for these properties.

However, I cannot seem to find a way to do this! After quite a bit of Googling and reading, I tried to specify the column type using DataAnnotations, from information found in this answer:

using System.ComponentModel.DataAnnotations;

...

[Column(TypeName = "Text")]
public string Body { get; set; }

This throws the following exception (when the database is deleted and the app is re-run):

Schema specified is not valid. Errors: (12,6) : error 0040: The Type text is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification.

But I have no idea what namespace or alias I should be specifying, and I couldn't find anything that would tell me.

I also tried changing the annotation as per this reference:

using System.Data.Linq.Mapping;

...

[Column(DbType = "Text")]
public string Body { get; set; }

In this case a database is created, but the Body column is still an NVarChar(4000), so it seems that annotation is ignored.

Can anyone help? This seems like it should be a fairly common requirement, yet my searching has been fruitless!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(12

苏辞 2024-10-22 17:56:41

您可以使用以下 DataAnnotation 和 Code-First 将生成数据库允许的最大大小的数据类型。对于 Sql CE,它会生成基础 ntext 列。

[MaxLength]

或使用 EF 4.1 RC Fluent API...

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Article>()
        .Property(p => p.Body)
        .IsMaxLength();
}

You can use the following DataAnnotation and Code-First will generate the maximum sized data type that the database allows. In the case of Sql CE it results in an underlying ntext column.

[MaxLength]

or using the EF 4.1 RC fluent API...

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Article>()
        .Property(p => p.Body)
        .IsMaxLength();
}
初见你 2024-10-22 17:56:41

您尝试过 ntext 吗?我刚刚创建了一个 SQL CE 4.0 数据库,当我手动向表中添加一列时,我注意到数据类型列表中不提供 text,而 ntext是。就像您可以选择 nvarchar 但不能选择 varchar 一样。

不幸的是,您可以选择的最大 nvarchar 大小是 4000。因此 nvarchar(max) 也不是一个选项。

有 ntext 但没有文本

Have you tried ntext? I have just created a SQL CE 4.0 database, and when I manually add a column to a table, I notice that text isn't available in the list of datatypes while ntext is. Just like you can pick nvarchar but no varchar.

Unfortunately, the biggest nvarchar size you can choose is 4000. So nvarchar(max) is also not an option.

There is ntext but no text

静若繁花 2024-10-22 17:56:41

您可以使用 System.ComponentModel.DataAnnotations.Schema .ColumnAttribute

[Column(TypeName="text")]
public string Text { get; set; }

或通过 Fluent API:

modelBuilder.Entity<YourEntityTypeHere>()
    .Property( e => e.Text)
    .HasColumnType("text");

You can use System.ComponentModel.DataAnnotations.Schema.ColumnAttribute

[Column(TypeName="text")]
public string Text { get; set; }

or via Fluent API:

modelBuilder.Entity<YourEntityTypeHere>()
    .Property( e => e.Text)
    .HasColumnType("text");
羅雙樹 2024-10-22 17:56:41

使用字符串长度属性的问题例如

[StringLength(4010)]

是否任何字符串>?属性中定义的字符数将触发验证异常,这违背了您在列上使用未定义的字段大小的任何原因,或者您在属性中使用了巨大的数字并丢失了该属性提供的任何验证属性。如果您使用 StringLength 属性,最终您将使用验证机制来解决映射问题,而 Marcel Popescu 使用 Column 属性的答案是一个更好的解决方案,因为它使用映射属性来定义类型,并且仍然允许您使用用于验证的 StringLength 属性。

另一种选择是使用 EF4 CTP5 流畅 API 并在 DbContext 中的 OnModelCreating 事件中定义列映射,例如

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Article>()
    .Property(p => p.Body)
    .HasColumnType("nvarchar(max)");
}

另外还应该注意的是,NText 是已弃用的数据类型 (ntext、文本和图像 (Transact-SQL) MS 联机丛书),建议使用 NVarChar(MAX) 代替

The issue with using the string length attribute e.g.

[StringLength(4010)]

Is that any string > the number of chars defined in the attribute will trigger a validation exception, which kind of goes against any reason why you would use a non defined field size on a column, or you use a huge number in the attribute and lose any validation offered by the attribute. Ultimately you are using a validation mechanism to solve a mapping issue if you use the StringLength attribute, where as Marcel Popescu's answer using the Column attribute is a much better solution as it is using the mapping attributes to define type, and still allows you to use the StringLength attribute for validation.

Another option is to use the EF4 CTP5 fluent API and define the column mapping in the OnModelCreating event in the DbContext e.g.

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Article>()
    .Property(p => p.Body)
    .HasColumnType("nvarchar(max)");
}

Also it should be noted that NText is a deprecated data type (ntext, text, and image (Transact-SQL) MS Books Online) and the recommendation is to use NVarChar(MAX) in its place

缺⑴份安定 2024-10-22 17:56:41

我知道,这可能已经晚了一年,但是:

使用:

[StringLength(-1)] 

这将创建一个 nText 字段。我设法在该字段中存储至少 25Kbytes 使用
Compact Edition 4.0 数据库。

I know, this is probably a year too late but:

Use:

[StringLength(-1)] 

This will create a nText field. I managed to store at least 25Kbytes in that field using
the Compact Edition 4.0 database.

衣神在巴黎 2024-10-22 17:56:41

您尝试过小写“text”吗?根据此 MSDN 讨论,数据提供者区分大小写。

Have you tried lowercase "text"? Per this MSDN discussion, the data provider is case sensitive.

(り薆情海 2024-10-22 17:56:41

此 DataAnnotation 将使 Code-First 在 sql 中生成 nvarchar(MAX) 列

[StringLength(1073741822)]

不确定其他大数字是否也会做同样的事情...我使用计算器和 nvarchar(MAX) 规范得到了这个。

我已经在 SQL Server 2005 Express 上尝试过,但没有在 CE 上尝试过,

我正在使用它并且它可以工作,但我想知道这是否是一个好主意,或者我是否遗漏了一些东西......有没有什么让代码优先知道我想要 nvarchar(MAX) 的其他方法?

This DataAnnotation will make Code-First generate a nvarchar(MAX) column in sql

[StringLength(1073741822)]

Not sure if other big numbers do the same... I got this one using the calculator and the nvarchar(MAX) spec.

I've tried it with SQL Server 2005 Express or not, but not with CE

I'm using it and it works, but I would like to know if it's a good idea or if I'm missing something... is there any other way to make code-first know that I want nvarchar(MAX)?

蘸点软妹酱 2024-10-22 17:56:41

此 DataAnnotation 将使 Code-First 在 sql 中生成 nvarchar(MAX) 列:)

[StringLength(4010)]

This DataAnnotation will make Code-First generate a nvarchar(MAX) column in sql also:)

[StringLength(4010)]
守望孤独 2024-10-22 17:56:41

同意 TypeName = "ntext" 似乎有效,尽管我还必须添加:

[StringLength(Int32.MaxValue)]

以阻止默认字符串长度 128 妨碍。

Agree that TypeName = "ntext" seems to work, although I also have to add:

[StringLength(Int32.MaxValue)]

to stop the default string length of 128 getting in the way.

薄暮涼年 2024-10-22 17:56:41

如果您使用 Package Manager 进行添加迁移和更新数据库,您可以通过添加 storeType 来修改创建表,如下所示:

       CreateTable(
            "dbo.Table_Name",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Title = c.String(nullable: false, maxLength: 500),
                    Body = c.String(unicode: false, storeType: "ntext"),
                })
            .PrimaryKey(t => t.ID);

In case you do add-migration and update-database using Package Manager, you may amend the create table by adding storeType as follows:

       CreateTable(
            "dbo.Table_Name",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Title = c.String(nullable: false, maxLength: 500),
                    Body = c.String(unicode: false, storeType: "ntext"),
                })
            .PrimaryKey(t => t.ID);
人间不值得 2024-10-22 17:56:41

如果您不想注释所有属性并且使用现代 EF,请使用约定:

public class StringNTextConvention : Convention {
  public StringNTextConvention() {
    Properties<string>().Configure(p => p.HasColumnType("ntext"));                    
  }
}

您可以从 onModelCreating() 中调用它:

modelBuilder.Conventions.Add(new StringNTextConvention());

以及所有 string s 将自动变成 ntext 列。

If you don't want to annotate all your properties and you use a contemporary EF, use a convention:

public class StringNTextConvention : Convention {
  public StringNTextConvention() {
    Properties<string>().Configure(p => p.HasColumnType("ntext"));                    
  }
}

You can call it from your onModelCreating():

modelBuilder.Conventions.Add(new StringNTextConvention());

and all your strings will automagically turn into ntext columns.

笛声青案梦长安 2024-10-22 17:56:40

我很欣赏现有答案所付出的努力,但我还没有发现它真正回答了这个问题......所以我测试了这个,并发现

[Column(TypeName = "ntext")]
public string Body { get; set; }

(来自 System.ComponentModel.DataAnnotations ) 将创建一个 ntext 类型列。

(我对接受的答案的问题是,它似乎表明您应该更改界面中的列类型,但问题是如何以编程方式执行此操作。)

I appreciate the effort that went into the existing answer, but I haven't found it actually answering the question... so I tested this, and found out that

[Column(TypeName = "ntext")]
public string Body { get; set; }

(the one from System.ComponentModel.DataAnnotations) will work to create an ntext type column.

(My problem with the accepted answer is that it seems to indicate that you should change the column type in the interface, but the question is how to do it programmatically.)

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文