4

I have an Entity Framework model (class) with a string property that I want to use as a key field. I have tried defining the property like this:

[IndexAttribute("SourceText", IsUnique=true)]
public string SourceText { get; set; }

This results in an SQL run-time error complaining that the property is invalid for use as a key field. I understand why, because unique keys must not be more than 8000 characters and string fields are potentially a lot longer than this. So, is there a way to tell EF that I don't need more than the first 500 characters from the field that is to be used as a key?

Robert Oschler
  • 14,153
  • 18
  • 94
  • 227
  • Try it perhaps with [Key] instead of [IndexAttribute("SourceText", IsUnique=true)] – oldsport Mar 29 '14 at 14:02
  • 2
    @Robert are you saying that you don't want to specify a max length on the column, but for the purpose of creating the index, you only want to use the first 500 chars of the column, even if its size is actually > 500? –  Mar 29 '14 at 14:05
  • 1
    @Cupcake Yes if that's possible. I've worked with other database frameworks that could do that so I'm hoping EF 6.1 can do that too. – Robert Oschler Mar 29 '14 at 14:28
  • @oldsport I don't see a "Key" attribute data annotation and VS doesn't recognize it either. – Robert Oschler Mar 29 '14 at 14:29
  • @Robert: Concerning the Key attribute -> add using System.ComponentModel.DataAnnotations; – oldsport Mar 29 '14 at 14:40
  • @oldsport Thanks. Now it recognizes the Key attribute, but the Key attribute doesn't seem to have a "unique" field? – Robert Oschler Mar 29 '14 at 15:31
  • @CupCake If you're interested, please rehash your comment as a reply with a StringLength attribute example so I can Accept it. I've decided to forego the idea of having the property (column) longer than the key and that's the solution I'm going to use. I just tested the StringLength/IndexAttribute (unique) combo and it worked. – Robert Oschler Mar 29 '14 at 16:48
  • 1
    @RobertOschler are you sure that you're ok with that solution? Have you considered the possibility of [including the large string column as part of another index](http://stackoverflow.com/a/2973568/456814)? (I think that's what these talk about, but I'm not sure). [900 byte index size limit in character length](http://stackoverflow.com/a/12717441/456814), [Create Indexes with Included Columns](http://msdn.microsoft.com/en-us/library/ms190806.aspx), [Using Included Columns to Avoid Size Limits](http://technet.microsoft.com/en-us/library/ms191241(v=sql.105).aspx). –  Mar 29 '14 at 22:20
  • 1
    @Cupcake Yes I am. The probability of getting a string greater than 500 chars long in the app context is less than 1% and the repercussions of truncating the string are nil. – Robert Oschler Mar 29 '14 at 23:15

1 Answers1

3

If you use the StringLengthAttribute data annotation to specify a max length on the string column that you're trying to index, that might work:

[Index("SourceText", IsUnique = true), StringLength(500)]
public string SourceText { get; set; }

However, I'm not entirely sure that that's the best solution. I don't know much about it, but apparently you can include large columns as part of a non-clustered index:

You can include nonkey columns in a nonclustered index to avoid the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. The SQL Server Database Engine does not consider nonkey columns when calculating the number of index key columns or the total size of the index key columns.

In a nonclustered index with included columns, the total size of the index key columns is restricted to 900 bytes. The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB. The columns in the INCLUDE clause can be of all data types, except text, ntext, and image.

but I wouldn't know off the top of my head how to do that with data annotations or the Fluent API.

Additional resources for including large columns in a non-clustered index

  1. How to resolve 900 key length limit index on the column which have datatype varchar(4096) in SQL Server 2005?
  2. 900 byte index size limit in character length
  3. Create Indexes with Included Columns
Community
  • 1
  • 1