Creating new entities

An entity or a table or a collection is defined by a class. The class must have int Id property which if 0 indicates new entity and if not - the entity to be updated. Supported data types are: int, double, DateTime (and their nullables), byte[] and string. (Note bool is not supported, use int instead). For example, we’ll use:

public class SomeData
{
    public int Id { get; set; }
    public int PeriodId { get; set; }
    public int? ObjectId { get; set; }
    public double Value { get; set; }
    public double? Normalized { get; set; }
    public int? PersonId { get; set; }
    public DateTime? Date { get; set; }
    public string Name { get; set; }
    public string NameSearch { get; set; }
}

Then to save new record we would (table will be created automatically if not there based on type's name):

using LinqDb;
...
var db = new Db("DATA");

var d = new SomeData()
{
    Id = 0,
    Normalized = 1.2,
    PeriodId = 5
};
db.Table<SomeData>().Save(d);
db.Dispose();

To save bulk of data more efficiently:

var list = new List<SomeData>();
...
db.Table<SomeData>().SaveBatch(list);

Querying

Select

Select everything from table:

List<SomeData> res = db.Table<SomeData>()
                       .SelectEntity();

To select only some columns:

var res = db.Table<SomeData>()
            .Select(f => new 
            { 
                Id = f.Id,
                Normalized = f.Normalized
            });


Note that only anonymous types are supported in this case. res is List<AnonymousType>

There is a limit in the amount of data that can be selected at once. If you need to select large amount there are non-atomic selects which would do the work in iterations. The drawback of this method is the data may change between iterations and the result might be mismatching initial select conditions.

Where

Linqdb supports Where clause:

var res = db.Table<SomeData>()
            .Where(f => f.Normalized == 2.3 || f.Date > DateTime.Now && f.PersonId == 5)
            .Select(f => new 
            { 
                Id = f.Id,
                Normalized = f.Normalized
            });

Where supports these operators: && || == >= > < <= != . Also on the left hand side of operator there must be some property without any expression (i.e. f.PersonId % 2 == 0 won‘t work), on the right hand side - constant/variable/expression. Types on both sides must match exactly (i.e. int != int?) and casting is only possible on the right side.
Empty string and empty array is treated as null.

Between

To evaluate something like .Where(f => f.Normalized > 3 && f.Normalized < 10) Linqdb will iterate data twice: first it will find all values > 3, then all values < 10 and finally return it’s intersection. So in this case it’s faster to use .Between(f => f.Normalized, 3, 10, BetweenBoundaries.BothExclusive) which will only scan data once: from value 3 to 10.

var res = db.Table<SomeData>()
            .Between(f => f.Normalized, 3, 10, BetweenBoundaries.BothExclusive)
            .Select(f => new 
            { 
                Id = f.Id,
                Normalized = f.Normalized
            });

Intersect

Finds intersection of values in table with given set:

var res = db.Table<SomeData>()
            .Intersect(f => f.Normalized, new HashSet<double>() { 10, 20, 30 })
            .Select(f => new
            {
                Id = f.Id,
                Normalized = f.Normalized
            });

Order, skip, take

Ordering (by one column) is supported:

var res = db.Table<SomeData>()
            .OrderBy(f => f.Normalized)
            .Skip(10)
            .Take(10)
            .Select(f => new
            {
                Id = f.Id,
                Normalized = f.Normalized
            });

Ordering by Id is the fastest.

There are also overloads of Select and SelectEntity that take object of type LinqdbSelectStatistics. This object will be populated with the number of records that satisfied condition. (Handy with .Skip and .Take when the total number is also needed). SearchedPercentile shows how much SearchTimeLimited has managed to search in given time.

Search

Linqdb supports simple full text search on string columns that end with ..Search (or .SearchS). For example,

var res = db.Table<SomeData>()
            .Search(f => f.NameSearch, "some text")
            .Select(f => new
            {
                Id = f.Id,
                Name = f.NameSearch
            });

will find rows where column NameSearch contains both "some" and "text".
.Search takes optional prameters start_step and steps which enable search on some part of documents. This is handy when you have lots of data and want to return some results as fast as possible. The search will only happen on slice of data, for example, start_step = 0, steps = 1 means only first 1000 documents will be searched, start_step = 10, steps = 5 - 5000 documents will be searched starting from document 10000. You can get total number of steps using LastStep function.

You can also limit search time by using SearchTimeLimited - it will search as much as it can in given time (how much was searched is in LinqdbSelectStatistics object's SearchedPercentile property).

You can make search by the start of word by using SearchPartial. In this case, however, you cannot search some data - the search will be performed on all rows.

If property name ends with ...SearchS the search index will only be using spaces to get words (as opposed to spaces and special characters). For example, string "hi@test me" would normaly satisfy search queries made of words: hi, test, me, hi@test. If, however, the property being searched ends with ...SearchS it will only satisfy queries containing these words: hi@test, me.

All operations on strings are case-insensitive.

Or

By default when statements like .Where or .Search go together they imply logical "and" to the results. It is possible to have "or" on neighbouring statements like so:

var res = db.Table<SomeData>()
            .Search(f => f.Name, "some text").Or().Search(f => f.Name, "something else")
            .Where(f => f.Id > 100)
            .Select(f => new
            {
                Id = f.Id,
                Name = f.Name
            });

In this case only one of search need to satisfy to return the result. More than one .Or could be used.

Intermediate results

All the work happens in .Select or .SelectEntity statement (generally - in the last statement), so things like these are possible:

var tmp = db.Table<SomeData>()
             .Where(f => f.Normalized == 5);
if (person_id != null)
{
    tmp.Where(f => f.PersonId == person_id); //no need to assign to tmp
}
var res = tmp.SelectEntity();

Count

To obtain table count:

db.Table<SomeData>().Count();

Also works when conditions applied:

db.Table<SomeData>().Where(f => f.Id < 50).Count();

GetIds

GetIds() method allows to get list of id's satisfying conditions without having to select them.

Updating

Save(item); will update row with item’s Id using item’s properties, given that Id is not 0. If it is 0, new item will be created and new Id will be assigned to the object’s Id property.

To update column of multiple rows you would need to construct Dictionary<int, T> where T is column’s type and:

var dic = new Dictionary<int, int?>();
dic[2] = 8;
dic[3] = 11;
db.Table<SomeData>().Update(f => f.PeriodId, dic);

This will update column PeriodId of rows with Ids 2 and 3 with respective values 8 and 11.

Deleting

To delete row(s) you would need to construct HashSet<int> of ids to be deleted and:

db.Table<SomeData>().Delete(new HashSet<int>() { 2, 3 });

Atomic increment

To increment values atomically. i.e. thread-safe, use .AtomicIncrement:

var new_item = new Counter() { Name = "unique_name", Value = 1 };
db.Table<Counter>().Where(f => f.Name == "unique_name").AtomicIncrement(f => f.Value, 1, new_item, null);

Transactions

Linqdb supports transactions:

using (var transaction = new LinqdbTransaction())
{
    var d = new SomeData()
    {
        Id = 1,
        Normalized = 1.2,
        PeriodId = 5
    };
    db.Table<SomeData>(transaction).Save(d); //note that .Table takes transaction as a parameter
    var d2 = new BinaryData()
    {
        Id = 1,
        Data = new List<byte>() { 1, 2, 3 }.ToArray()
    };
    db.Table<BinaryData>(transaction).Save(d2);
    transaction.Commit(); //all writes happen here, if it fails - nothing gets modified (all or nothing)
}

if .Commit is not called, nothing is modified. Transaction must be created used and destroyed in the same thread. After .Save (or .SaveBatch) inside a transaction new items (with Id == 0) are assigned new ids, so that they can be used before doing commit. Transaction is supported on these data-modifying commands: .Save, .SaveBatch, .Update, .Delete.

Indexes

Linqdb supports in-memory indexes on columns of type int, DateTime and double. In-memory indexes speed up reading operations on such columns. Also indexes are required for .GroupBy statement (see below). To build an index on property use .CreatePropertyMemoryIndex, to remove - .RemovePropertyMemoryIndex Indexes slow down data-modifying operations, so use them only when you have to. If indexes (or searchable string properties) are present it is especially efficient to use batch operations, i.e. .SaveBatch and to make batches larger.

GroupBy

Suppose you want to group by column PeriodId and aggregate column Value. For that in-memory index must be created:

db.Table<SomeData>().CreateGroupByMemoryIndex(f => f.PeriodId, f => f.Value);

Then you could do something like:

var res = db.Table<SomeData>()
            .GroupBy(f => f.PeriodId)
            .Select(f => new { f.Key, Sum = f.Sum(z => z.Value), Avg = f.Average(z => z.Value) });

These aggregation functions are supported: Count, CountDistinct, Sum, Max, Min, Average. Key selects group-by property's value.

Generic types

Linqdb supports working on generic types: db.Table<T>().


Non-atomic modifications

There is a certain limit on batch sizes in modification operations, so that they could be performed atomically (all or nothing). If there is a need to modify large amount of data and atomicity is not important there are convenience methods: SaveNonAtomically, UpdateNonAtomically, DeleteNonAtomically. These cannot be used in a transaction.


Server status

GetServerStatus(int timeToWaitInMs = 1000) returns server's name (servername property in config.txt) and also returns no later than timeToWaitInMs ms. If server doesn't respond sooner it is considered down.


Queues

Simple, but fast in-memory queues are supported: db.Queue<T>().PutToQueue adds item to queue T and db.Queue<T>().GetAllFromQueue gets all queue's T items and removes them from queue. Only one reader is quaranteed to get specific item in multi-threaded environment. Entities used in a queue must be marked with protobuf attributes.


Init \ dispose

Before using Linqdb one would need to create it:

var db = new Db("DATA"); //embedded version var db = new Db("host_ip:port"); //server's client version

The argument is path to the database or ip to the server.
The db needs to be created once at the application startup.
Before exiting application it is a good practice to dispose the db:

db.Dispose();

Replication

To make a copy of a database programatically:

db.Replicate("PATH_TO_COPY");

If directory exists - it will be removed before copying. Database can still be read/written while replication is in progress.

Limitations \ caveats

Server installation

On Windows server folder contains Server.exe which starts listening on port specified in config.txt
Server has configuration file config.txt which is self-explanatory.
You could install server as Windows service using NSSM.