SeviceStack.Ormlite 接口对应SQL

一、特殊属性特性

模型类属性上可以加特性，控制其是否参与SQL语句，需要引用名称空间using ServiceStack.DataAnnotations;

其中IgnoreDataMember是不参与json，需要引用名称空间using System.Runtime.Serialization;

例如：

【注意】：默认模型类中出现的第一个属性为主键，若为派生类则是派生类中第一个属性，若需要特别标明为主键，可添加特性[PrimaryKey]

二、测试代码结构

using (IDbConnection db = sqlServerFactory.OpenDbConnection())
{
      try
      {
            //每次执行的ormlite函数
      }
      catch (Exception ex)
      {
                    
      }
}

三、同步接口

1、类图

2、模型类结构

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
}

3、SQL转换

db.Insert(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 });

exec sp_executesql N'INSERT INTO "Person" ("Id","FirstName","LastName","Age") VALUES (@Id,@FirstName,@LastName,@Age)',N'@Id int,@FirstName varchar(4),@LastName varchar(7),@Age int',@Id=1,@FirstName='Jimi',@LastName='Hendrix',@Age=27

---

db.InsertOnly(() => new Person { FirstName = "Amy" });

exec sp_executesql N'INSERT INTO "Person" ("FirstName") VALUES (@FirstName)',N'@FirstName varchar(3)',@FirstName='Amy'

【注意】：可能会违反完整性约束

---

对应上一条，另一种写法

var q = db.From<Person>()
    .Insert(p => new { p.FirstName });

db.InsertOnly(new Person { FirstName = "Amy" }, onlyFields: q)

exec sp_executesql N'INSERT INTO "Person" ("FirstName") VALUES (@FirstName)',N'@FirstName varchar(3)',@FirstName='Amy'

【注意】：需要引用using ServiceStack.OrmLite.Legacy; 否则会提示类型转换错误

---

db.Update(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 });

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName, "LastName"=@LastName, "Age"=@Age WHERE "Id"=@Id',N'@Id int,@FirstName varchar(4),@LastName varchar(7),@Age int',@Id=1,@FirstName='Jimi',@LastName='Hendrix',@Age=27

---

db.Update(new Person { Id = 1, FirstName = "JJ" }, p => p.LastName == "Hendrix");

exec sp_executesql N'UPDATE "Person" SET "Id"=@Id, "FirstName"=@FirstName, "LastName"=@LastName, "Age"=@Age WHERE ("LastName" = @0)',N'@0 varchar(8000),@Id int,@FirstName varchar(2),@LastName varchar(8000),@Age int',@0='Hendrix',@Id=1,@FirstName='JJ',@LastName=NULL,@Age=NULL

【注意】：未赋值的属性可能会设为null

---

db.Update<Person>(new { FirstName = "JJ" }, p => p.LastName == "Hendrix");

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName WHERE ("LastName" = @0)',N'@0 varchar(8000),@FirstName varchar(2)',@0='Hendrix',@FirstName='JJ'

---

db.UpdateNonDefaults(new Person { FirstName = "JJ" }, p => p.LastName == "Hendrix");

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName WHERE ("LastName" = @0)',N'@0 varchar(8000),@FirstName varchar(2)',@0='Hendrix',@FirstName='JJ'

 【注意】：未赋值的属性不会设为null，效果等同于上一条匿名方式

---

db.UpdateOnly(() => new Person { FirstName = "JJ" });

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName',N'@FirstName varchar(2)',@FirstName='JJ'

【注意】：全表修改，没有where条件

---

db.UpdateOnly(() => new Person { FirstName = "JJ" }, where: p => p.LastName == "Hendrix");

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName WHERE ("LastName" = @0)',N'@0 varchar(8000),@FirstName varchar(2)',@0='Hendrix',@FirstName='JJ'

---

db.UpdateOnly(new Person { FirstName = "JJ" }, onlyFields: p => p.FirstName);

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName',N'@FirstName varchar(2)',@FirstName='JJ'

---

db.UpdateOnly(new Person { FirstName = "JJ", Age = 12 }, 
    onlyFields: p => new { p.FirstName, p.Age });

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName, "Age"=@Age',N'@FirstName varchar(2),@Age int',@FirstName='JJ',@Age=12

---

db.UpdateOnly(new Person { FirstName = "JJ", Age = 12 }, 
    onlyFields: p => new[] { "FirstName", "Age" });

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName, "Age"=@Age',N'@FirstName varchar(2),@Age int',@FirstName='JJ',@Age=12

---

db.UpdateOnly(new Person { FirstName = "JJ" }, 
    onlyFields: p => p.FirstName, 
    where: p => p.LastName == "Hendrix");

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName WHERE ("LastName" = @0)',N'@0 varchar(8000),@FirstName varchar(2)',@0='Hendrix',@FirstName='JJ'

---

var q = db.From<Person>()
    .Update(p => p.FirstName);

db.UpdateOnly(new Person { FirstName = "JJ", LastName = "Hendo" }, onlyFields: q);

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName',N'@FirstName varchar(2)',@FirstName='JJ'

---

var updateFields = new Dictionary<string,object> {
    [nameof(Person.FirstName)] = "JJ",
};

db.UpdateOnly<Person>(updateFields, p => p.LastName == "Hendrix");

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName WHERE ("LastName" = @0)',N'@0 varchar(8000),@FirstName varchar(2)',@0='Hendrix',@FirstName='JJ'

---

var q = db.From<Person>()
    .Where(x => x.FirstName == "Jimi")
    .Update(p => p.FirstName);
          
db.UpdateOnly(new Person { FirstName = "JJ" }, onlyFields: q);

exec sp_executesql N'UPDATE "Person" SET "FirstName"=@FirstName WHERE ("FirstName" = @0)',N'@0 varchar(8000),@FirstName varchar(2)',@0='Jimi',@FirstName='JJ'

---

db.UpdateAdd(() => new Person { Age = 3 }); 

exec sp_executesql N'UPDATE "Person" SET "Age"="Age"+@Age',N'@Age int',@Age=3

【注意】：是字段原有值+

---

//Remove 5 points from Jackson Score
db.UpdateAdd(() => new Person { Age = -5 }, where: x => x.LastName == "Jackson");

exec sp_executesql N'UPDATE "Person" SET "Age"="Age"+@Age WHERE ("LastName" = @0)',N'@0 varchar(8000),@Age int',@0='Jackson',@Age=-5

---

//Add 10 points to Michael's age
var q = db.From<Person>()
    .Where(x => x.FirstName == "Michael");
db.UpdateAdd(() => new Person { Age= 10 }, q);

exec sp_executesql N'UPDATE "Person" SET "Age"="Age"+@Age WHERE ("FirstName" = @0)',N'@0 varchar(8000),@Age int',@0='Michael',@Age=10

---

db.Select<Person>(x => x.Age >= 2 && x.Age <= 10);

exec sp_executesql N'SELECT "Id", "FirstName", "LastName", "Age" 
FROM "Person"
WHERE (("Age" >= @0) AND ("Age" <= @1))',N'@0 int,@1 int',@0=2,@1=10

【注意】：不是转换为between and，执行效果也没区别

---

db.Select<Person>(x => Sql.In(x.LastName, "London", "Madrid", "Berlin"));

exec sp_executesql N'SELECT "Id", "FirstName", "LastName", "Age" 
FROM "Person"
WHERE "LastName" IN (@0,@1,@2)',N'@0 varchar(8000),@1 varchar(8000),@2 varchar(8000)',@0='London',@1='Madrid',@2='Berlin'

---

db.Select<Person>(x => x.LastName.StartsWith("A"));

exec sp_executesql N'SELECT "Id", "FirstName", "LastName", "Age" 
FROM "Person"
WHERE upper("LastName") like @0',N'@0 varchar(8000)',@0='A%'

---

db.Select<Person>(x => x.LastName.Contains("Benedict"));

exec sp_executesql N'SELECT "Id", "FirstName", "LastName", "Age" 
FROM "Person"
WHERE upper("LastName") like @0',N'@0 varchar(8000)',@0='%BENEDICT%'

---

db.Select<Person>(x => "Rate " + x.Age == "Rate 10"); //server string concatenation

exec sp_executesql N'SELECT "Id", "FirstName", "LastName", "Age" 
FROM "Person"
WHERE ((''Rate '' + "Age") = @0)',N'@0 varchar(8000)',@0='Rate 10'

【注意】：Rate只是字符串值，不是属性名称

---

db.SingleById<Person>(1);

exec sp_executesql N'SELECT "Id", "FirstName", "LastName", "Age" FROM "Person" WHERE "Id" = @Id',N'@Id int',@Id=1

【注意】：主键是哪个属性，依赖于模型类中的设置

---

db.Single<Person>(x => x.Age == 42);

exec sp_executesql N'SELECT TOP 1 "Id", "FirstName", "LastName", "Age" 
FROM "Person"
WHERE ("Age" = @0)',N'@0 int',@0=42

【注意】：如果查询结果为null，返回的实体类也是null

---

var q = db.From<Person>()
          .Where(x => x.Age > 40)
          .Select(Sql.Count("*"));

int peopleOver40 = db.Scalar<int>(q);

exec sp_executesql N'SELECT COUNT(*) 
FROM "Person"
WHERE ("Age" > @0)',N'@0 int',@0=40

---

db.Count<Person>(x => x.Age < 50);

exec sp_executesql N'SELECT COUNT(*) 
FROM "Person"
WHERE ("Age" < @0)',N'@0 int',@0=50

---

db.Exists<Person>(new { Age = 42 });

exec sp_executesql N'SELECT "Id", "FirstName", "LastName", "Age" FROM "Person" WHERE "Age" = @Age',N'@Age int',@Age=42

【注意】：运行的sql语句确实这样的，但函数返回值是布尔型

---

db.Scalar<Person, int>(x => Sql.Max(x.Age), x => x.Age < 50);

exec sp_executesql N'SELECT Max("Age") 
FROM "Person"
WHERE ("Age" < @0)',N'@0 int',@0=50

---

var q = db.From<Person>()
    .Where(x => x.Age == 27)
    .Select(x => x.LastName);
    
List<string> results = db.Column<string>(q);

exec sp_executesql N'SELECT "LastName" 
FROM "Person"
WHERE ("Age" = @0)',N'@0 int',@0=27

---

var q = db.From<Person>()
          .Where(x => x.Age < 50)
          .Select(x => x.Age);

HashSet<int> results = db.ColumnDistinct<int>(q);

exec sp_executesql N'SELECT "Age" 
FROM "Person"
WHERE ("Age" < @0)',N'@0 int',@0=50

---

var q = db.From<Person>()
          .Where(x => x.Age < 50)
          .Select(x => new { x.Id, x.LastName });

Dictionary<int,string> results = db.Dictionary<int, string>(q);

exec sp_executesql N'SELECT "Id", "LastName" 
FROM "Person"
WHERE ("Age" < @0)',N'@0 int',@0=50

 【注意】：理论上key应该对应的是主键列

---

var q = db.From<Person>()
          .Where(x => x.Age < 50)
          .Select(x => new { x.Age, x.LastName });

Dictionary<int, List<string>> results = db.Lookup<int, string>(q);

exec sp_executesql N'SELECT "Age", "LastName" 
FROM "Person"
WHERE ("Age" < @0)',N'@0 int',@0=50

【注意】：可以用来进行分组

---

var q = db.From<Person>()
    .GroupBy(x => x.LastName)
    .Select(x => new { x.LastName, Count = Sql.Count("*") })
    .OrderByDescending("Count");

var results = db.KeyValuePairs<string, int>(q);

SELECT "LastName", Count(*) AS Count 
FROM "Person"
GROUP BY "LastName"
ORDER BY Count DESC

【注意】：很奇怪，这条sql语句执行了两次

---

public class FullDogBowl
{
    public int DogId { get; set; }
    public int BowlId { get; set; }
    public string Breed { get; set; }
    public string Name { get; set; }
    public string Type { get; set; }
    public string Color { get; set; }
}

var dogBowl = db.Select<FullDogBowl>(db
    .From<Dog>()
    .Join<Dog, DogBowl>((d, db) => d.Id == db.DogId)
    .Join<DogBowl, Bowl>((db, b) => db.BowlId == b.Id)
    .Where<Dog>(d => d.Id == 5))
    .ToList();

注意join后的泛型类型顺序是交叉的，如果反了会有问题

等同于

string sql = @"select
    db.DogId,
    db.BowlId,
    d.Name AS DogName,
    b.Color as BowlColor
from DogBowl db
join dog d on db.DogId = d.Id
join bowl b on db.BowlId = b.Id
where d.Id = @dog_id ";

var dogBowlList = db.SqlList<DogBowl>(sql, new { dog_id = 5, });

---

选取特定列

var q = db.From<DeptEmployee>()
    .Join<Department2>()
    .Select<DeptEmployee, Department2>(
        (de, d2) => new { de.FirstName, de.LastName, d2.Name });

结果加载到DeptEmployee类中，必须保证有列举的列名相同的属性

若去掉join，Department2改为DeptEmployee，则可以只选取一个表中的特定列