user1 user1 - 1 month ago 14
C# Question

When is the Index attribute used on a Sqlite.Net class?

I am building a mobile application using

Sqlite.Net
and I have come across the
Indexed
attribute. This is shown in the example here: https://github.com/praeclarum/sqlite-net#example-time

public class Stock
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[MaxLength(8)]
public string Symbol { get; set; }
}

public class Valuation
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[Indexed]
public int StockId { get; set; }
public DateTime Time { get; set; }
public decimal Price { get; set; }
}


as you can see the
StockId
column is marked as
Indexed
but when querying the tables it will surely be up to the
Sqlite
engine to determine the indexes and optimise querying.

So my question is what is the
Indexed
attribute used for and do I need it?

Answer

but when querying the tables it will surely be up to the Sqlite engine to determine the indexes

No, indexes have to be predefined and are maintained with Insert, Update and Delete. When a query runs, the engine can decide which indexes to use or ignore but not to create one.

So the [Indexed] attribute on the foreign key is a proper manual optimization.