FreeSql

Table of Contents

1. Navigation Property Table
2. Complex multi-table join
3、WithoutJoin
4. SQL join table
5. Subtable: Exists
6. Subtable: In
7. Subtable: Join
8. Subtable: First/Count/Sum/Max/Min/Avg
9、Subtable: ToList
10. Collection properties
11. WhereCascade
Reference

中文 | English

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
    .Build(); //Be sure to define as singleton mode

class Topic {
    [Column(IsIdentity = true)]
    public int Id { get; set; }
    public string Title { get; set; }
    public int Clicks { get; set; }
    public DateTime CreateTime { get; set; }

    public int CategoryId { get; set; }
    public Category Category { get; set; }
}
class Category {
    [Column(IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }

    public int ParentId { get; set; }
    public CategoryType Parent { get; set; }
    public List<Topic> Topics { get; set; }
}
class CategoryType {
    public int Id { get; set; }
    public string Name { get; set; }
}

fsql.Select<Topic>()
  .LeftJoin(a => a.Category.Id == a.CategoryId)
  .LeftJoin(a => a.Category.Parent.Id == a.Category.ParentId)
  .Where(a => a.Category.Parent.Id > 0)
  .ToList();
//SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`, a__Category.`Id` as6, a__Category.`Name`, a__Category.`ParentId`
//FROM `Topic` a
//LEFT JOIN `Category` a__Category ON a__Category.`Id` = a.`CategoryId`
//LEFT JOIN `CategoryType` a__Category__Parent ON a__Category__Parent.`Id` = a__Category.`ParentId`

Tip: After the navigation relationship is configured correctly, you no longer need to manually call LeftJoin.

2. Complex multi-table join

fsql.Select<Topic, Category, CategoryType>()
  .LeftJoin((a,b,c) => a.CategoryId == b.Id)
  .LeftJoin((a,b,c) => b.ParentId == c.Id)
  .Where((a,b,c) => c.Id > 0)
  .ToList((a,b,c) => new { a,b,c });

//OR
fsql.Select<Topic>().From<Category, CategoryType>((s, b, c) => s
  .LeftJoin(a => a.CategoryId == b.Id)
  .LeftJoin(a => b.ParentId == c.Id))
  .Where((a,b,c) => c.Id > 0)
  .ToList((a,b,c) => new { a,b,c });

//WITHOUT DEFINE a, b, c
fsql.Select<Topic, Category, CategoryType>()
  .LeftJoin(w => w.t1.CategoryId == w.t2.Id)
  .LeftJoin(w => w.t2.ParentId == w.t3.Id)
  .Where(w => w.t3.Id > 0)
  .ToList(w => new { w.t1,w.t2,w.t3 });

//SELECT ...
//FROM `Topic` a
//LEFT JOIN `Category` b ON a.`CategoryId` = b.`Id`
//LEFT JOIN `CategoryType` c ON b.`ParentId` = c.`Id`
//WHERE c. `Id` > 0

Experience: One-to-many, only the last record is taken from the associated table

3、WithoutJoin

fsql.Select<Order, Product, User>()
    .InnerJoin((o, p, u) => o.UserId == u.Id)
    .LeftJoin((o, p, u) => o.ProductId == p.Id)
    .WithoutJoin(t2: !includeProductInfo)
    .ToList((o, p, u) => new OrderDetailDto
    {
        OrderId = o.Id,
        OrderNo = o.OrderNo,
        Username = u.Username, // User (T3) Always JOIN, can be directly referenced
        ProductName = includeProductInfo ? p.Name : "N/A"
    });

// includeProductInfo = true: SQL JOIN Product
// includeProductInfo = false: SQL will not JOIN Product, ProductName will be "N/A"

4. SQL join table

fsql.Select<Topic>()
  .LeftJoin("Category b on b.Id = a.CategoryId and b.Name = @bname", new { bname = "xxx" })
  .ToList();

//SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
//FROM `Topic` a
//LEFT JOIN Category b on b.Id = a.CategoryId and b.Name = @bname

Extended question: How to specify the fields of the SQL join table b in ToList?

.ToList(a => new {
  bid = Convert.ToInt32("b.Id"),
  bName = "b.Name"
})

5. Subtable: Exists

fsql.Select<Topic>()
  .Where(a => fsql.Select<Topic>().As("b").Where(b => b.Id == a.Id).Any())
  .ToList();

//SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
//FROM `Topic` a
//WHERE (exists(SELECT 1
//    FROM `Topic` b
//    WHERE (b.`Id` = a.`Id`)
//    limit 0,1))

Tip: Since the entity class of the subquery is the same as the upper layer, use As("b") to specify the alias to distinguish.

6. Subtable: In

fsql.Select<Topic>()
  .Where(a => fsql.Select<Topic>().As("b").ToList(b => b.Id).Contains(a.Id))
  .ToList();

//SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
//FROM `Topic` a
//WHERE (((a.`Id`) in (SELECT b.`Id`
//    FROM `Topic` b)))

7. Subtable: Join

Since version 1.8.0, string.Join + ToList can concatenate the results of multiple rows of sub-queries into one string, such as: "1,2,3,4":

fsql.Select<Topic>().ToList(a => new {
  id = a.Id,
  concat = string.Join(",", fsql.Select<StringJoin01>().ToList(b => b.Id))
});

//SELECT a.`Id`, (SELECT group_concat(b.`Id` separator ',') 
//    FROM `StringJoin01` b) 
//FROM `Topic` a

Tip: The subquery string.Join + ToList is adapted to sqlserver/pgsql/oracle/mysql/sqlite/firebird/达梦/金仓/南大/翰高 #405

8. Subtable: First/Count/Sum/Max/Min/Avg

fsql.Select<Category>().ToList(a => new  {
  all = a,
  first = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).First(b => b.Id),
  count = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Count(),
  sum = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Sum(b => b.Clicks),
  max = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Max(b => b.Clicks),
  min = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Min(b => b.Clicks),
  avg = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Avg(b => b.Clicks)
});

9、Subtable: ToList

v3.2.650+

fsql.Select<Topic>().ToList(a => new
{
  all = a,
  list1 = fsql.Select<T2>().ToList(),
  list2 = fsql.Select<T2>().Where(b => b.TopicId == a.Id).ToList()
});

fsql.Select<Topic>()
  .GroupBy(a => new { a.Author })
  .WithTempQuery(a => new { Author = a.Key.Author, Count = a.Count() })
  .ToList(a => new {
    a.Author, a.Count,
    list1 = fsql.Select<T2>().ToList(),
    list2 = fsql.Select<T2>().Where(b => b.Author == a.Author).ToList()
  });

10. Collection properties

fsql.Select<Category>()
  .Where(a => a.Topics.Any(b => b.Title.Contains("xx"))) //v3.2.600 Within a.Topics.AsSelect()
  .ToList();

The effect is equivalent to:

fsql.Select<Category>()
  .Where(a => fsql.Select<Topic>().Any(b => b.Title.Contains("xx")))
  .ToList();

Quickly convert the collection properties to ISelect for sub-query operations.

11. WhereCascade

When querying multiple tables, it is troublesome to add conditions to each table like ISelect, and WhereCascade came into being. After using WhereCascade, when SQL is generated, all tables will be attached to this condition.

Such as:

fsql.Select<t1>()
  .LeftJoin<t2>(...)
  .WhereCascade(x => x.IsDeleted == false)
  .ToList();

The SQL obtained is:

SELECT ...
FROM t1
LEFT JOIN t2 on ... AND (t2.IsDeleted = 0) 
WHERE t1.IsDeleted = 0

It will only take effect when the entity can attach expressions, and supports sub-table query. The more tables used in a single query, the greater the benefits.

Applicable to:

Subqueries, one-to-many, many-to-many, and custom sub-queries;
Join query, navigation properties, custom Join query;
Include/IncludeMany sub-collection query;

The propagation of Lazy Property is temporarily not supported;

This function is different from Filter, this function is used for the propagation of single multi-table query conditions;

1. Navigation Property Table