FreeSql、AutoMapper处理多对多查询映射。

参考：https://www.cnblogs.com/FreeSql/p/11531404.html

          https://blog.csdn.net/dotnetCore/article/details/101733455

          https://www.cnblogs.com/youring2/p/automapper.html

1、定义的实体及Dto

public class UserEntity
{
	public long Id { get; set; }
	
	//导航属性，ManyToMany
	[Navigate(ManyToMany = typeof(UserOrganizeRelEntity))]
	public List<OrganizeEntity> Organizes { get; set; }
}

public class OrganizeEntity
{
	public long Id { get; set; }
	
	/// <summary>
	/// 关系表
	/// </summary>
	public List<UserOrganizeRelEntity> Rel { get; set; }

	//导航属性，ManyToMany
	[Navigate(ManyToMany = typeof(UserOrganizeRelEntity))]
	public List<UserEntity> Users { get; set; }
}

public class UserOrganizeRelEntity
{
	public long UserId { get; set; }

	public long OrganizeId { get; set; }

	[Navigate("UserId")]
	public UserEntity User { get; set; }

	[Navigate("OrganizeId")]
	public OrganizeEntity Organize { get; set; }
}

public class OrganizeDto
{
    public long Id { get; set; }

    public List<UserDto> Users { get; set; }
}

2、不使用导航属性时，主要展示AutoMapper的映射，此时可以不使用Navigate，但从UserEntity映射到UserDto时，使用AutoMapper进行映射，稍微麻烦一点。

//FreeSql
var ar = await this.freeSql.Select<OrganizeEntity>()
    .IncludeMany(a => a.Rel, then => then.Include(t => t.User))
    .ToListAsync().ConfigureAwait(false);

//AutoMapper
x.CreateMap<UserEntity, UserDto>();
x.CreateMap<OrganizeEntity, OrganizeDto>()
//.ForMember(d => d.Users, opt => opt.ResolveUsing<CustomResolver>());
//.ForMember(d => d.Users, opt => opt.ResolveUsing(new CustomResolver()));
//.ForMember(d => d.Users, z => z.ResolveUsing(o => o.Rel.Select(t => t.User).ToList()));
.ForMember(y => y.Users, z => z.MapFrom(o => o.Rel.Select(t => t.User).ToList()));

public class CustomResolver : IValueResolver<OrganizeEntity, OrganizeDto, List<UserRelDto>>
{
	public List<UserRelDto> Resolve(OrganizeEntity source, OrganizeDto destination, List<UserRelDto> destMember, ResolutionContext context)
	{
		//Mapper not initialized.
		//Call Initialize with appropriate configuration.
		//If you are trying to use mapper instances through a container or otherwise, 
		//make sure you do not have any calls to the static Mapper.Map methods, 
		//and if you're using ProjectTo or UseAsDataSource extension methods, 
		//make sure you pass in the appropriate IConfigurationProvider instance.
		//try
		//{
		//    var list = source.Rel.Select(x => x.User).ToList();
		//    return AutoMapper.Mapper.Map<List<UserRelDto>>(list);
		//}
		//catch(Exception ex)
		//{
		//    string err = ex.ToString();
		//    return null;
		//}
		
		return source.Rel.Select(x => new UserRelDto
		{
			Id = x.User.Id,
		}).ToList();
	}
}

3、使用FreeSql导航属性时,不再需要AutoMapper中的复杂映射，OrganizeEntity中的Users就有数据了。相对方便一些，但在OrganizeEntity和UserEntity中均需要设置[Navigate(ManyToMany = typeof(UserOrganizeRelEntity))]，中间表也需要[Navigate("OrganizeId")], 以确定多对多导航关系。

//FreeSql
var ar = await this.freeSql.Select<OrganizeEntity>()
		  .IncludeMany(a => a.Users)
		  .ToListAsync().ConfigureAwait(false);

4、使用延时加载，定义时需要如：public virtual Order Order { get; set; }，注意加上virtual，并将UseLazyLoading设为true

5、使用自动同步实体结构到数据库UseAutoSyncStructure(true)，字符串长度，超过255时，需要手动指定[Column(DbType = "varchar(max)")]，否则存储超过255的一些数据时，数据库的字段会变小，出现截断错误。

6、FreeSql中不要使用嵌套事务，否则外部事务会报错。

7、一对多，一对一

public class GroupInfoEntity
{
	/// <summary>
	/// 
	/// </summary>
	public long Id { get; set; }
	/// <summary>
	/// 列表数据
	/// </summary>
	//[Navigate("SetId")]
	public List<InfoEntity> InfoList { get; set; }
}

public class InfoEntity
{
	/// <summary>
	/// 标识符
	/// </summary>
	public long Id { get; set; }
	
	/// ID
	/// </summary>
	public long SetId { get; set; }
	

	public GroupInfoEntity GroupInfo { get; set; }
	
	public PosEntity Pos { get; set; }
}

public class PosEntity
{
	/// <summary>
	/// 标识符
	/// </summary>
	public long Id { get; set; }
	
	public long InfoId { get; set; } 		
}

//1、需要加[Navigate("SetId")]
//var dbSet = this.dbContext.Set<GroupInfoEntity>();
//var entity = dbSet.Select
//    .IncludeMany<InfoEntity>(a => a.InfoList)
//    .Where(x => x.Id == id).ToOne();


// 2、未加[Navigate("SetId")]，因为是一对多
// 还是应该用IncludeMany，不过需要在where中指定关联的字段
var entity = this.dbContext._freeSql.Select<GroupInfoEntity>()
	 用于一对一，此处获取不到列表InfoList
	//.LeftJoin<InfoEntity>((x, y) => x.Id == y.SetId)
	 报错
	//.LeftJoin(x => x.InfoList.Any(y => y.SetId == x.Id))
	.IncludeMany<InfoEntity>(a => a.InfoList.Where(y => a.Id == y.SetId)
	   , then => then.Include<PosEntity>(a => a.Pos)
                .LeftJoin(a => a.Id == a.Pos.InfoId))
	.Where(x => x.Id == id).ToOne();

//3、一对一
var t = this.dbContext._freeSql.Select<InfoEntity>()
     //与.LeftJoin(x => x.SetId == x.GroupInfo.Id)类似
     //.Include<GroupInfoEntity>(a => a.GroupInfo)
     //.LeftJoin(a => a.SetId == a.GroupInfo.Id)
	 .LeftJoin(x => x.SetId == x.GroupInfo.Id)
	 .Where(x => x.SetId == id).ToOne();

//4、一对一，但是没有3好
var t1 = this.dbContext._freeSql.Select<InfoEntity>()
	 .LeftJoin< GroupInfoEntity>((x, y) => x.SetId == y.Id)
	 .Where(x => x.SetId == id).ToOne();