讨论新的查询语法

[2881099]

@LambertW

[Table(Name = "xxx", SelectFilter = " a.id > 0")]
	class TestInfo {
		[Column(IsIdentity = true, IsPrimary = true)]
		public int Id { get; set; }
		public int TypeGuid { get; set; }
		public TestTypeInfo Type { get; set; }
		public string Title { get; set; }
		public DateTime CreateTime { get; set; }
	}

	class TestTypeInfo {
		[Column(IsIdentity = true)]
		public int Guid { get; set; }
		public int ParentId { get; set; }
		public TestTypeParentInfo Parent { get; set; }
		public string Name { get; set; }
	}

	class TestTypeParentInfo {
		public int Id { get; set; }
		public string Name { get; set; }

		public int ParentId { get; set; }
		public TestTypeParentInfo Parent { get; set; }

		public List<TestTypeInfo> Types { get; set; }
	}

[2881099]

public class Order {
			[Column(IsPrimary = true)]
			public int Id { get; set; }
			public string OrderTitle { get; set; }
			public string CustomerName { get; set; }
			public DateTime TransactionDate { get; set; }
			public virtual List<OrderDetail> OrderDetails { get; set; }
		}
		public class OrderDetail {
			[Column(IsPrimary = true)]
			public int Id { get; set; }

			public int OrderId { get; set; }
			public virtual Order Order { get; set; }
		}


var collSelect1 = g.mysql.Select<Order>().Where(a =>
	a.OrderDetails.AsSelect().Any(b => b.Id > 100)
).ToSql();

SELECT a.`Id`, a.`OrderTitle`, a.`CustomerName`, a.`TransactionDate` 
FROM `Order` a 
WHERE (exists(SELECT 1 
FROM `OrderDetail` b 
WHERE (b.`Id` > 100) AND (b.`OrderId` = a.`Id`) 
limit 0,1))

[LambertW]

不太认同由FreeSql实现太复杂的语法。

1. 如果需要全面实现导航属性，那可能EF会是我第一考虑的框架。
2. 过于复杂或者存在有争议的时候，需要写ToSql()进行验证通过才敢使用，开发效率会降低。
3. 目前可能还是考虑简单的Select<entity1, entity2>((a,b) => {})的实现思路，更复杂就直接使用SQL。

[LambertW]

我们是FreeSql框架的使用者，可能对于它的理解，没办法跟您一样有深刻的认知。
所以太过深入的语法，会有风险

[2881099]

只要做足测试，其实还好。我建了分支在做，目前差不多可以实现。
OneToOne、ManyToOne 的导航属性，自动 leftjoin，同对象的属性控制了重复join的可能。

OneToMany 目前是 exists 查询。

ManyToMany 也快好了。

实体类的关系（非外键）已经建立好了，包括错误的友好提醒。

[2881099]

由于自动导出了查询，之前的单元测试有些地方使用导航属性的，会报错，断言的SQL内容发生了变化。

[2881099]

@systemhejiyong

[2881099]

分支：NavigateProperty

目前在 mysql 上实现的，有兴趣可以测试一下效果。

var parentSelect1 = select.Where(a => a.Type.Parent.Parent.Parent.Parent.Name == "").Where(b => b.Type.Name == "").ToSql();

SELECT a.`Id`, a.`TypeGuid`, a__Type.`Guid`, a__Type.`ParentId`, a__Type.`Name`, a.`Title`, a.`CreateTime` 
FROM `xxx` a 
LEFT JOIN `TestTypeInfo` a__Type ON a__Type.`Guid` = a.`TypeGuid` 
LEFT JOIN `TestTypeParentInfo` a__Type__Parent ON a__Type__Parent.`Id` = a__Type.`ParentId` 
LEFT JOIN `TestTypeParentInfo` a__Type__Parent__Parent ON a__Type__Parent__Parent.`Id` = a__Type__Parent.`ParentId` 
LEFT JOIN `TestTypeParentInfo` a__Type__Parent__Parent__Parent ON a__Type__Parent__Parent__Parent.`Id` = a__Type__Parent__Parent.`ParentId` 
LEFT JOIN `TestTypeParentInfo` a__Type__Parent__Parent__Parent__Parent ON a__Type__Parent__Parent__Parent__Parent.`Id` = a__Type__Parent__Parent__Parent.`ParentId` 
WHERE (a__Type__Parent__Parent__Parent__Parent.`Name` = '') AND (a__Type.`Name` = '') AND ( a.id > 0)

---

var collSelect1 = g.mysql.Select<Order>().Where(a =>
	a.OrderDetails.AsSelect().Any(b => b.Id > 100)
);

SELECT a.`Id`, a.`OrderTitle`, a.`CustomerName`, a.`TransactionDate` 
FROM `Order` a 
WHERE (exists(SELECT 1 
	FROM `OrderDetail` b 
	WHERE (b.`Id` > 100) AND (b.`OrderId` = a.`Id`) 
	limit 0,1))

---

var collectionSelect = select.Where(a => 
	//a.Type.Guid == a.TypeGuid &&
	//a.Type.Parent.Id == a.Type.ParentId &&
	a.Type.Parent.Types.AsSelect().Where(b => b.Name == a.Title).Any(
		//b => b.ParentId == a.Type.Parent.Id
	)
);

SELECT a.`Id`, a.`TypeGuid`, a__Type.`Guid`, a__Type.`ParentId`, a__Type.`Name`, a.`Title`, a.`CreateTime` 
FROM `xxx` a 
LEFT JOIN `TestTypeInfo` a__Type ON a__Type.`Guid` = a.`TypeGuid` 
LEFT JOIN `TestTypeParentInfo` a__Type__Parent ON a__Type__Parent.`Id` = a__Type.`ParentId` 
WHERE (exists(SELECT 1 
	FROM `TestTypeInfo` b 
	WHERE (b.`Name` = a.`Title`) AND (b.`ParentId` = a__Type__Parent.`Id`) 
	limit 0,1)) AND ( a.id > 0)

---

var collectionSelect2 = select.Where(a =>
	a.Type.Parent.Types.AsSelect().Where(b => b.Name == a.Title).Any(
		b => b.Parent.Name == "xxx" && b.Parent.Parent.Name == "ccc"
		&& b.Parent.Parent.Parent.Types.AsSelect().Any(cb => cb.Name == "yyy")
	)
);

SELECT a.`Id`, a.`TypeGuid`, a__Type.`Guid`, a__Type.`ParentId`, a__Type.`Name`, a.`Title`, a.`CreateTime` 
FROM `xxx` a 
LEFT JOIN `TestTypeInfo` a__Type ON a__Type.`Guid` = a.`TypeGuid` 
LEFT JOIN `TestTypeParentInfo` a__Type__Parent ON a__Type__Parent.`Id` = a__Type.`ParentId` 
WHERE (a__Type.`Guid` = a.`TypeGuid`) AND (a__Type__Parent.`Id` = a__Type.`ParentId`) AND (exists(SELECT 1 
	FROM `TestTypeInfo` b 
	LEFT JOIN `TestTypeParentInfo` b__Parent ON b__Parent.`Id` = b.`ParentId` 
	LEFT JOIN `TestTypeParentInfo` b__Parent__Parent ON b__Parent__Parent.`Id` = b__Parent.`ParentId` 
	LEFT JOIN `TestTypeParentInfo` b__Parent__Parent__Parent ON b__Parent__Parent__Parent.`Id` = b__Parent__Parent.`ParentId` 
	WHERE (b.`Name` = a.`Title`) AND (b__Parent.`Name` = 'xxx' AND b__Parent__Parent.`Name` = 'ccc' AND exists(SELECT 1 
		FROM `TestTypeInfo` cb 
		WHERE (cb.`Name` = 'yyy') AND (cb.`ParentId` = b__Parent__Parent__Parent.`Id`) 
		limit 0,1)) AND (b.`ParentId` = a__Type__Parent.`Id`) 
	limit 0,1)) AND ( a.id > 0)

---

var collectionSelect3 = select.Where(a =>
	a.Type.Parent.Types.AsSelect().Where(b => b.Name == a.Title).Any(
		bbb => bbb.Parent.Types.AsSelect().Where(lv2 => lv2.Name == bbb.Name + "111").Any(
		)
	)
);

SELECT a.`Id`, a.`TypeGuid`, a__Type.`Guid`, a__Type.`ParentId`, a__Type.`Name`, a.`Title`, a.`CreateTime` 
FROM `xxx` a 
LEFT JOIN `TestTypeInfo` a__Type ON a__Type.`Guid` = a.`TypeGuid` 
LEFT JOIN `TestTypeParentInfo` a__Type__Parent ON a__Type__Parent.`Id` = a__Type.`ParentId` 
WHERE (exists(SELECT 1 
	FROM `TestTypeInfo` b 
	LEFT JOIN `TestTypeParentInfo` b__Parent ON b__Parent.`Id` = b.`ParentId` 
	WHERE (b.`Name` = a.`Title`) AND (exists(SELECT 1 
		FROM `TestTypeInfo` lv2 
		WHERE (lv2.`Name` = concat(b.`Name`, '111')) AND (lv2.`ParentId` = b__Parent.`Id`) 
		limit 0,1)) AND (b.`ParentId` = a__Type__Parent.`Id`) 
	limit 0,1)) AND ( a.id > 0)

---

[Table(Name = "xxx", SelectFilter = " a.id > 0")]
class TestInfo {
	[Column(IsIdentity = true, IsPrimary = true)]
	public int Id { get; set; }
	public int TypeGuid { get; set; }
	public TestTypeInfo Type { get; set; }
	public string Title { get; set; }
	public DateTime CreateTime { get; set; }
}
class TestTypeInfo {
	[Column(IsIdentity = true)]
	public int Guid { get; set; }
	public int ParentId { get; set; }
	public TestTypeParentInfo Parent { get; set; }
	public string Name { get; set; }
}
class TestTypeParentInfo {
	public int Id { get; set; }
	public string Name { get; set; }
	public int ParentId { get; set; }
	public TestTypeParentInfo Parent { get; set; }
	public List<TestTypeInfo> Types { get; set; }
}

我目前重点测试这几个用例，都调通了。

[2881099]

public partial class Song {
	[Column(IsIdentity = true)]
	public int Id { get; set; }
	public DateTime? Create_time { get; set; }
	public bool? Is_deleted { get; set; }
	public string Title { get; set; }
	public string Url { get; set; }

	public virtual ICollection<Tag> Tags { get; set; }
}
public partial class Song_tag {
	public int Song_id { get; set; }
	public virtual Song Song { get; set; }

	public int Tag_id { get; set; }
	public virtual Tag Tag { get; set; }
}
public partial class Tag {
	[Column(IsIdentity = true)]
	public int Id { get; set; }
	public int? Parent_id { get; set; }
	public virtual Tag Parent { get; set; }

	public decimal? Ddd { get; set; }
	public string Name { get; set; }

	public virtual ICollection<Song> Songs { get; set; }
	public virtual ICollection<Tag> Tags { get; set; }
}


[Fact]
public void AsSelect() {
	//OneToOne、ManyToOne
	var t0 = g.mysql.Select<Tag>().Where(a => a.Parent.Parent.Name == "粤语").ToSql();
	//SELECT a.`Id`, a.`Parent_id`, a__Parent.`Id` as3, a__Parent.`Parent_id` as4, a__Parent.`Ddd`, a__Parent.`Name`, a.`Ddd` as7, a.`Name` as8 
	//FROM `Tag` a 
	//LEFT JOIN `Tag` a__Parent ON a__Parent.`Id` = a.`Parent_id` 
	//LEFT JOIN `Tag` a__Parent__Parent ON a__Parent__Parent.`Id` = a__Parent.`Parent_id` 
	//WHERE (a__Parent__Parent.`Name` = '粤语')

	//OneToMany
	var t1 = g.mysql.Select<Tag>().Where(a => a.Tags.AsSelect().Any(t => t.Parent.Id == 10)).ToSql();
	//SELECT a.`Id`, a.`Parent_id`, a.`Ddd`, a.`Name` 
	//FROM `Tag` a 
	//WHERE (exists(SELECT 1 
	//	FROM `Tag` t 
	//	LEFT JOIN `Tag` t__Parent ON t__Parent.`Id` = t.`Parent_id` 
	//	WHERE (t__Parent.`Id` = 10) AND (t.`Parent_id` = a.`Id`) 
	//	limit 0,1))

	//ManyToMany
	var t2 = g.mysql.Select<Song>().Where(s => s.Tags.AsSelect().Any(t => t.Name == "国语")).ToSql();
	//SELECT a.`Id`, a.`Create_time`, a.`Is_deleted`, a.`Title`, a.`Url` 
	//FROM `Song` a
	//WHERE(exists(SELECT 1
	//	FROM `Song_tag` Mt_Ms
	//	WHERE(Mt_Ms.`Song_id` = a.`Id`) AND(exists(SELECT 1
	//		FROM `Tag` t
	//		WHERE(t.`Name` = '国语') AND(t.`Id` = Mt_Ms.`Tag_id`)
	//		limit 0, 1))
	//	limit 0, 1))
}

最新测试查询 @LambertW @systemhejiyong @htrlq