多对多 中间关联表，配置了特性 one to one ，生成的 sql 缺少双引号 返回
SqlSugar
5
102
Just a man 发布于2周前
悬赏：0 飞吻
= await db.Queryable<TagAudio>().Where(a => _tagIds.Contains(a.TagId) || (a.Tag.RootTags.Any(b => _tagIds.Contains(b.Id))))
.Select<AudioOut>(a => new AudioOut { TagId = a.TagId, Id = a.AudioId, TagSequence = a.Sequence, Type = a.Audio.Type, Name = a.Audio.Name })
.ToListAsync();然后生成的语句中，as 后面 有双引号，但 on 没有，导致提示 “对于表"tagsubtag_1",丢失FROM子句项 ”
SELECT "tagId" AS "TagId" , "audioId" AS "Id" , "sequence" AS "TagSequence" ,
(SELECT "type" FROM "audio" WHERE "a"."audioId"="id" ) AS "Type" ,
(SELECT "name" FROM "audio" WHERE "a"."audioId"="id" ) AS "Name" FROM "tag_audio" "a"
WHERE (("tagId" IN (16,148,2,10,12,13,144,146,17,3,14,11,1,4,5,6,9,7,15,18)) OR
EXISTS( ( SELECT 1 FROM "tag" "tag0" Inner JOIN "tag_subtag" "TagSubtag_1" ON TagSubtag_1."subTagId"=tag0."id" Inner JOIN "tag" "tag1" ON tag1."id"=TagSubtag_1."tagId" WHERE a."tagId" = tag0."id" AND (tag1."id" IN (16,148,2,10,12,13,144,146,17,3,14,11,1,4,5,6,9,7,15,18)) ) ) )注意看最后一排的tagsubtag_1
热忱回答（5）
-
Just a man VIP0
2周前
大佬，望修复下，谢谢了
0 回复 -
fate sta VIP0
2周前升级最新预览版本看一下
0 回复 -
fate sta VIP0
2周前
SqlSugarCore 5.1.3.46-preview04
0 回复 -
fate sta VIP0
2周前
如果还有问题，把实体类几个都发出来
0 回复 -
Just a man VIP0
2周前
大佬，我试过了，还是存在问题；
下面是执行语句。where 里面的a.Tag.RootTags.Any() 出现问题的地方；
_tagIds :标签表的 id 集合（定级标签）
where 表示，查找指定定级标签关联的 Audio，或是指定定级标签相关的下级标签关联的 Audio
Tag 和 Audio 多对多，Tag和 Tag 本身 也存在多对多表示上下级关系
var allAudios = await db.Queryable<TagAudio>() .Where(a => _tagIds.Contains(a.TagId) || (a.Tag.RootTags.Any(b => _tagIds.Contains(b.Id)))) .Select<AudioOut>(a => new AudioOut { TagId = a.TagId, Id = a.AudioId, TagSequence = a.Sequence, Type = a.Audio.Type, Name = a.Audio.Name }) .ToListAsync();实体分别如下：
中间表
using System; using System.Collections.Generic; using System.Linq; using SqlSugar; namespace LiveDbContext { /// <summary> /// ///</summary> [SugarTable("tag_audio")] public class TagAudio { /// <summary> /// ///</summary> [SugarColumn(ColumnName = "tagId", IsPrimaryKey = true)] public int TagId { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "audioId", IsPrimaryKey = true)] public int AudioId { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "sequence")] public int? Sequence { get; set; } [Navigate(NavigateType.OneToOne, nameof(TagId))] public Tag Tag { get; set; } [Navigate(NavigateType.OneToOne, nameof(AudioId))] public Audio Audio { get; set; } } }实体表 Audio
using System; using System.Collections.Generic; using System.Linq; using SqlSugar; namespace LiveDbContext { /// <summary> /// ///</summary> [SugarTable("audio")] public class Audio { /// <summary> /// ///</summary> [SugarColumn(ColumnName = "id", IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "name")] public string Name { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "src")] public string Src { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "duration")] public int? Duration { get; set; } /// <summary> /// 类型 0:本地 1:云音效 2:云背景 ///</summary> [SugarColumn(ColumnName = "type")] public int? Type { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "userId")] public int? UserId { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "sequence")] public int? Sequence { get; set; } /// <summary> /// 状态 0:未转换 1:转换中 2:已转换 /// </summary> [SugarColumn(ColumnName = "state")] public int State { get; set; } [Navigate(typeof(TagAudio), nameof(TagAudio.AudioId), nameof(TagAudio.TagId))]//注意顺序 public List<Tag> Tags { get; set; }//只能是null不能赋默认值哈喽 [Navigate(NavigateType.OneToOne, nameof(UserId))]//一对一 public User User { get; set; } } }实体表：Tag
using System; using System.Collections.Generic; using System.Linq; using SqlSugar; namespace LiveDbContext { /// <summary> /// ///</summary> [SugarTable("tag")] public class Tag { /// <summary> /// ///</summary> [SugarColumn(ColumnName = "id", IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "name")] public string Name { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "sequence")] public int? Sequence { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "userId")] public int? UserId { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "isSub")] public bool IsSub { get; set; } /// <summary> /// 用于用户创建时候的同步数据使用 ///</summary> [SugarColumn(IsIgnore = true)] public int OldId { get; set; } [Navigate(typeof(TagAudio), nameof(TagAudio.TagId), nameof(TagAudio.AudioId))]//注意顺序 public List<Audio> Audios { get; set; }//只能是null不能赋默认值 [Navigate(typeof(TagVideo), nameof(TagVideo.TagId), nameof(TagVideo.VideoId))]//注意顺序 public List<Video> Videos { get; set; }//只能是null不能赋默认值 [Navigate(typeof(TagTeleprompter), nameof(TagTeleprompter.TagId), nameof(TagTeleprompter.TeleprompterId))]//注意顺序 public List<Teleprompter> Teleprompters { get; set; }//只能是null不能赋默认值 [Navigate(NavigateType.OneToOne, nameof(UserId))]//一对一 public User User { get; set; } [Navigate(typeof(TagSubtag), nameof(TagSubtag.SubTagId), nameof(TagSubtag.TagId))]//注意顺序 public List<Tag> RootTags { get; set; }//只能是null不能赋默认值 [Navigate(typeof(TagSubtag), nameof(TagSubtag.TagId), nameof(TagSubtag.SubTagId))]//注意顺序 public List<Tag> SubTags { get; set; } //只能是null不能赋默认值 } }实体表：TagSubtag
using System; using System.Collections.Generic; using System.Linq; using SqlSugar; namespace LiveDbContext { /// <summary> /// ///</summary> [SugarTable("tag_subtag")] public class TagSubtag { /// <summary> /// ///</summary> [SugarColumn(ColumnName = "tagId", IsPrimaryKey = true)] public int TagId { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "subTagId", IsPrimaryKey = true)] public int SubTagId { get; set; } /// <summary> /// ///</summary> [SugarColumn(ColumnName = "sequence")] public int? Sequence { get; set; } [Navigate(NavigateType.OneToOne, nameof(TagId))] public Tag Tag { get; set; } [Navigate(NavigateType.OneToOne, nameof(SubTagId))] public Tag SubTag { get; set; } } }0 回复