using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using ServiceStack.OrmLite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using ServiceStack.OrmLite.Sqlite;

var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var db = dbFactory.Open(); // Open ADO.NET DB Connection

[Alias("Dic")]
public class Dic : IHasId<int>
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public int DicNum { get; set; }

    public int DicCode { get; set; }

    public string DicDecode { get; set; }

    public string Description { get; set; }
}

[Alias("Predmet")]
public class Subject : IHasId<int>
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [Alias("PType")]
    public int ParentDicType { get; set; }

    [Alias("PCode")]
    public int ChildDicCode { get; set; }

    [Alias("PValue")]
    public string Value { get; set; }

    [Alias("PDescription")]
    public string Description { get; set; }

    [References(typeof(Person))]
    public Nullable<long> PersonId { get; set; }

    public Dic Parent { get; set; }

    public Dic Child { get; set; }
}

[Alias("Person")]
public class Person : IHasId<long>
{
    [PrimaryKey, AutoIncrement]
    public long Id { get; set; }
    public string Pib { get; set; }
    [Alias("Bd")]
    public Nullable<int> BirthdaySingle { get; set; }
    [Alias("Inn")]
    public string IdentityCode { get; set; }
    [Alias("Bp")]
    public string BirthPlace { get; set; }
    [Alias("Lp")]
    public string LivePlace { get; set; }
    [Alias("Doc")]
    public string Document { get; set; }
    [Alias("Gr")]
    public string Nationality { get; set; }
    [Alias("Org")]
    public string Organization { get; set; }
    [Alias("Pseudo")]
    public string Pseudonym { get; set; }
    [Reference]
    public List<Subject> Subjects { get; set; }
}

long CountByCondition<T>(Expression<Func<T, bool>> predicate) where T: class
{
    try
    {
        // bug: OrmLite !string.IsNullOrWhiteSpace(someStringValue) function causes error while building predicate
        /*var q = Database.From<T>();
        q = q.Where(predicate);
        string cnt = q.ToCountStatement();
        string slt = q.ToSelectStatement();
        return Database.Scalar<long>(cnt, q.Params);
        return id;*/
        return db.Count<T>(predicate);
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error in executing query:" + Environment.NewLine);
        Console.WriteLine(ex.Message);
    }
    return -1;
}

Expression<Func<Person, bool>> GetSecondLambda()
{
    Expression<Func<Person, bool>> secondLambda = pvm => pvm.Pib.Contains("BDV") &&
        pvm.BirthdaySingle == 19880427 &&
        pvm.IdentityCode.Contains("454185662") &&
        pvm.BirthPlace.Contains("USA") &&
        pvm.LivePlace.Contains("NEW YORK") &&
        pvm.Document.Contains("QWE17277ASD") &&
        pvm.Nationality.Contains("USA") &&
        pvm.Organization.Contains("INDUSTRIES") &&
        pvm.Pseudonym.Contains("AGAPIT") &&
        Sql.In(pvm.Id, db.From<Subject>().Where(svm => (svm.ParentDicType == 10000 &&
                (svm.Value.Contains("063") ||
                    svm.Value.Contains("099") ||
                    svm.Value.Contains("050"))) &&
            (svm.ParentDicType == 11000 && svm.Value.Contains("AA7495CH")) &&
            (svm.ParentDicType == 12000 && svm.Value.Contains("PM-9")) &&
            (svm.ParentDicType == 13000 && svm.Value.Contains("QWERTY@MAIL.RU")) &&
            (svm.ParentDicType == 14000 && svm.Value.Contains("VK.COM")) &&
            (svm.ParentDicType == 15000 && svm.Value.Contains("TEST")))
            .SelectDistinct(svm => svm.PersonId));
    return secondLambda;
}

Console.Write("Trying to run second query...");
var secondCount = CountByCondition(GetSecondLambda());
if (secondCount != -1)
{
    Console.WriteLine("Success");
}