Handling indexes on tables in OpenAccess?

[Kendall Bennett]

I finally got my model converted over from Entity Framework to OpenAccess, and I noticed that when I updated the model from the database, it pulled in all the information about the table indexes into the RLINQ file. But when I examine the model from the GUI design tools, I don't see any references to the indexes nor how to manage them? Is there some way to add/delete indexes and manage indexes on tables using the OpenAccess designer, or does it just pull them in from the database for reference purposes?

[Alexander]

Hi Kendall Bennett,

Your observations are correct. The indexes are imported in the rlinq model but currently there is no way to manipulate them with the designer. They are kept only for reference purposes during runtime.

Regards,
Alexander
the Telerik team

[Kendall Bennett]

Ok thanks. It would be really nice to be able to maintain them in the designer, or at least see what is indexed :)

[Igor]

Hello!

i faced with the problem, that might be related to the topic starter question.
I am using TelerikORM (added model with visual designer) with MySQL backend, and adding the index in MySQL like that:

alter table table_1 add index (date);

afterwards, when i am performing Telerik ORM action "Updated database from model", it generates instruction to drop index:

-- Dropping index 'date' which is not configured in OpenAccess but exists on the database.
ALTER TABLE `table_1` DROP INDEX `date`;

i dont expect this index to be dropped.
i found this topic in the manual: http://www.telerik.com/help/openaccess-orm/fluent-mapping-mapping-clr-advanced-defining-indexes.html
but i am interesting, if there is any option to create indexe via Telerik ORM Designer?

[Andrey]

Hi, Igor!

You can add created index in MySql into the model: In the designer, select "Update from Database", and then select the target index.
After that, when you call the "Updated database from model", removal index instructions will not.

[Igor]

hi Andrey, thank you for your answer.
i might not want to create index manually in the database, do you know if OpenAccessORM could handle that for me?
Should i use the instruction like this:

productConfiguration.HasIndex(x => x.Price)
   .IsUnique()
   .IsClustered()
   .WithName("IX_Products_Price");

in generated by OpenAccess ORM code or there is another option to accomplish the same?

[Yordan]

Hello Igor,

There are three ways of adding indexes in the database using OpenAccess ORM.

1) If Fluent Mapping is used the way indexes can be added is to write additional code in the PrepareMapping() method. In that method for every configuration indexes can be added with code similar to the one that is pasted in the previous post.

2) If RLINQ file is used indexes can be added by overriding the OnDatabaseOpen() method in the model class - that is the class that inherits form OpenAccessContext class. For example if an index on table Products have to be added based on the column UnitPrice the code could be similar to the following:

protected override void OnDatabaseOpen(BackendConfiguration backendConfiguration, MetadataContainer metadataContainer)
{
    MetaTable metaTableProduct = metadataContainer.Tables.First(t => t.Name.Equals("Products"));
    MetaIndex metaIndex = new MetaIndex("MyIndex", metaTableProduct);
    MetaColumn metaColumnUnitPrice = metaTableProduct.Columns.First(c => c.Name.Equals("UnitPrice"));
    metaIndex.Columns.Add(new MetaIndexColumnMapping("MyColumn", metaColumnUnitPrice, 1, SortOrder.Ascending));
    metaIndex.Clustered = true;
    metaTableProduct.Indexes.Add(metaIndex);
    metadataContainer.Indexes.Add(metaIndex);

    base.OnDatabaseOpen(backendConfiguration, metadataContainer);
}

In the same class the method UpdateSchema have to be defined too.

public void UpdateSchema()
{
    var handler = this.GetSchemaHandler();
    string script = null;
    try
    {
        script = handler.CreateUpdateDDLScript(null);
    }
    catch
    {
        bool throwException = false;
        try
        {
            handler.CreateDatabase();
            script = handler.CreateDDLScript();
        }
        catch
        {
            throwException = true;
        }
        if (throwException)
        {
            throw;
        }
    }
    if (string.IsNullOrEmpty(script) == false)
    {

        handler.ForceExecuteDDLScript(script);

    }
}

After that the code to force the creation of the index in the database is:

using (EntitiesModel dbContext = new EntitiesModel())
{
    dbContext.UpdateSchema();
}

3) There is a third options how indexes can be added - by editing the XML file of the model - the RLINQ file. This option however is prone to errors since no intellisense can be used and the structure of the XML file can be corrupted which will lead to errors. 

If there are any more questions implementing one of the fore-mentioned scenarios please do not hesitate to write back to us.

Kind regards,
Yordan
the Telerik team

[Igor]

Thank you, Yordan! This is what i was looking for

[Tuan Hoang]

I am using RIA OpenAccess, where I can call UpdateSchema();
Sorry for newbie question.

Tuan Hoang Anh

[Tuan Hoang]

In my OnDatabaseOpen, i add new index and script = handler.CreateUpdateDDLScript(null) alway return null. Why it alway return null ?

[Kristian Nikolov]

Hello Tuan,

Please find the answers to your questions as follows.

Calling UpdateSchema() in a RIA Service scenario:
After you have extended the context of your model (the class which inherits from OpenAccessContext) with the UpdateSchema method, you have to expose it through your domain service. To do that, extend your domain service class and add a method which calls UpdateSchema for the DataContext object of the class:

public void SchemaUpdate()
{
    this.DataContext.UpdateSchema();
}

Then, after rebuilding your solution you should be able to access the SchemaUpdate method through your client. Do note however, that UpdateSchema updates the database to be in sync with the current state of your model and enabling such functionality through the client is generally not recommended.

CreateUpdateDDLScript(null) always returns null:
When using a Domain Model (.rlinq file) this behavior is normal. The reason for it is that by default changes made to the entities do not result in schema updates. To alter this behavior, open the Visual Designer and in the properties window of the desired domain class set the Update Schema property to true. Afterwards the CreateUpdateDDLScript method should generate the appropriate script for adding an index to the database.

I hope this helps. Should you have additional questions, feel free to contact us again.

Regards,
Kristian Nikolov
Telerik

 

 

[Tuan Hoang]

Thanks Kristian Nikolov. It work now.