Adding indexed fields from the designer

Topics: EF Designer
Nov 21, 2012 at 12:42 PM

I've not seen any discussion about adding the ability to specify an indexed field in the Entity Framework Designer.  How are others working around this deficiency?

Dec 3, 2012 at 10:38 PM

Can you clarify what the "indexed field" is?

Thanks,
Pawel

Dec 4, 2012 at 11:51 AM

Another ORM tool that I've used allows me to set a boolean property for fields in the object model. This boolean property indicates whether or not a database index should be created for this field/column in the underlying table.  The tool then takes care of creating the index when the database is generated.

To work around this in Entity Framework I've been creating another sql file that contains the commands to create the desired indexes. From that point on, whenever I execute the *.edmx.sql file I also execute my sql file to create the indexes.  It sure would be nice if commands for creating the indexes were included in the *.edmx.sql file.  Seems like it would be a fairly easy enhancement to implement.

Jan 22, 2013 at 8:13 AM

The problem with this is that you end up with a beginner understanding of SQL.

For example:

* Your index then has only one field. GRATULATIONS.

* You can not use any of the advanced index functionality SQL Server has.

Not sure what you work on, but for me the whole generation is already unusable - I Run large data conversion scripts and generally use a lot of functionality that is clear and nice in SQL Server, but not exposed in any means. Among them filtered indices (common: 2 indices, same field, one non-unique for value null, one unique for value non null). What about included fields in indices?

Jan 22, 2013 at 4:05 PM

@vhayman: Sorry I missed your comment and did not reply earlier.

There is a way to customize how the gerenated Sql code looks like. You can create a custom element in the CSDL that will have CopyToSSDL attribute in edmx v2 namespace (this is actually a bug since even if you are using edmx v3 you still need to use edmxv2 namespace - we have a bug on this: http://entityframework.codeplex.com/workitem/702). Then you would create your custom T4 template (based on the one that is included in VS - SSDLToSQL10.tt) that generates the Sql code so that it understands the extended properties and generate Sql accordingly. I found a walk through that does it:

https://sites.google.com/site/mrmbookmarks/msg/entityframework-customizingthegenerateddatamodelscript

Having said that extending the EF designer this way is obscure and difficult. I created a work item (http://entityframework.codeplex.com/workitem/807) for adding support for indexes in the EFDesigner.

Thanks,

Pawel