SqlFunc导致的42P01: 对于表"m",丢失FROM子句项” 返回
SqlSugar
处理中
6
146
=_= 发布于2周前
悬赏：0 飞吻
using Newtonsoft.Json;
using SqlSugar;
namespace Test
{
public class Program
{
private static void Main(string[] args)
{
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.PostgreSQL,
ConnectionString = "PORT=5432;DATABASE=test4;HOST=localhost;PASSWORD=odoo;USERID=postgres",
IsAutoCloseConnection = true
});
db.CodeFirst.InitTables<Customer>();
db.CodeFirst.InitTables<Quotation>();
var today = DateTime.Now.Date;
var yesterday = today.AddDays(-1);
var firstDayOfMonth = new DateTime(today.Year, today.Month, 1);
var firstDayOfLastMonth = firstDayOfMonth.AddMonths(-1);
var firstDayOfYear = new DateTime(today.Year, 1, 1);
var firstDayOfLastYear = firstDayOfYear.AddYears(-1);
// Customer 数据
var customerInfo = db.Queryable<Customer>()
.Where(m => m.Active == "Y")
//.Where(m=>SqlFunc.MappingColumn<bool>(" ( \"m\".ownerusers && ARRAY['001'] ) OR ( NOT ( \"m\".relatedusers IS NULL OR \"m\".relatedusers = '{}' ) AND ( \"m\".relatedusers && ARRAY['001'] ) )"))
.Select(m => new
{
CustomerCount = SqlFunc.AggregateCount(m.Id),
TodayCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Date == today, 1, 0)),
YesterdayCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Date == yesterday, 1, 0)),
MonthCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Year == today.Year && m.CreateTime.Month == today.Month, 1, 0)),
LastMonthCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Year == firstDayOfLastMonth.Year && m.CreateTime.Month == firstDayOfLastMonth.Month, 1, 0)),
YearCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Year == today.Year, 1, 0)),
LastYearCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Year == firstDayOfLastYear.Year, 1, 0)),
DealCustomerToday = SqlFunc.AggregateSum(SqlFunc.IIF(
SqlFunc.Subqueryable<Quotation>()
.Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Date == today)
.Any(), 1, 0)),
DealCustomerYesterday = SqlFunc.AggregateSum(SqlFunc.IIF(
SqlFunc.Subqueryable<Quotation>()
.Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Date == yesterday)
.Any(), 1, 0)),
DealCustomerMonth = SqlFunc.AggregateSum(SqlFunc.IIF(
SqlFunc.Subqueryable<Quotation>()
.Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" &&
q.DealOrNotDate.Year == today.Year && q.DealOrNotDate.Month == today.Month)
.Any(), 1, 0)),
DealCustomerLastMonth = SqlFunc.AggregateSum(SqlFunc.IIF(
SqlFunc.Subqueryable<Quotation>()
.Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" &&
q.DealOrNotDate.Year == firstDayOfLastMonth.Year && q.DealOrNotDate.Month == firstDayOfLastMonth.Month)
.Any(), 1, 0)),
DealCustomerYear = SqlFunc.AggregateSum(SqlFunc.IIF(
SqlFunc.Subqueryable<Quotation>()
.Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Year == today.Year)
.Any(), 1, 0)),
DealCustomerLastYear = SqlFunc.AggregateSum(SqlFunc.IIF(
SqlFunc.Subqueryable<Quotation>()
.Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Year == firstDayOfLastYear.Year)
.Any(), 1, 0))
})
.First();
Console.WriteLine(JsonConvert.SerializeObject(customerInfo));
}
public class Permission
{
/// <summary>
/// 唯一编号
/// </summary>
[SugarColumn(ColumnDescription = "唯一编号", Length = 50, IsPrimaryKey = true, IsNullable = false)]
public string Id { get; set; } = string.Empty;
/// <summary>
/// 单据类型
/// </summary>
[SugarColumn(ColumnDescription = "单据类型", Length = 50, IsNullable = false)]
public string Type { get; set; } = string.Empty;
/// <summary>
/// 创建人
/// </summary>
[SugarColumn(ColumnDescription = "创建人", Length = 50, IsNullable = false)]
public string CreateUser { get; set; } = string.Empty;
/// <summary>
/// 创建人
/// </summary>
[SugarColumn(ColumnDescription = "创建时间", IsNullable = false)]
public DateTime CreateTime { get; set; } = DateTime.Now;
/// <summary>
/// 更新人
/// </summary>
[SugarColumn(ColumnDescription = "更新人", Length = 50, IsNullable = true)]
public string UpdateUser { get; set; } = string.Empty;
/// <summary>
/// 更新时间
/// </summary>
[SugarColumn(ColumnDescription = "更新时间", IsNullable = true)]
public DateTime UpdateTime { get; set; } = DateTime.Now;
/// <summary>
/// 归档标志
/// </summary>
[SugarColumn(ColumnDescription = "归档标志", Length = 1, IsNullable = false)]
public string Active { get; set; } = "Y";
/// <summary>
/// 部门关联
/// </summary>
[SugarColumn(ColumnDescription = "部门关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)]
public string[] RelatedDepartments { get; set; } = Array.Empty<string>();
/// <summary>
/// 用户关联
/// </summary>
[SugarColumn(ColumnDescription = "用户关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)]
public string[] RelatedUsers { get; set; } = Array.Empty<string>();
/// <summary>
/// 所有者
/// </summary>
[SugarColumn(ColumnDescription = "所有者", ColumnDataType = "text[]", IsArray = true, IsNullable = false)]
public string[] OwnerUsers { get; set; } = Array.Empty<string>();
}
[SugarTable("link_customer")]
public class Customer : Permission
{
/// <summary>
/// 客户编号
/// </summary>
[SugarColumn(ColumnDescription = "客户编号", Length = 50, IsNullable = false)]
public string CustomerId { get; set; } = string.Empty;
/// <summary>
/// 客户名称
/// </summary>
[SugarColumn(ColumnDescription = "客户名称", Length = 200, IsNullable = false)]
public string Name { get; set; } = string.Empty;
/// <summary>
/// 公司编号
/// </summary>
[SugarColumn(ColumnDescription = "公司编号", Length = 50, IsNullable = false)]
public string CompanyId { get; set; } = string.Empty;
/// <summary>
/// 部门编号
/// </summary>
[SugarColumn(ColumnDescription = "部门编号", Length = 50, IsNullable = false)]
public string DepartmentId { get; set; } = string.Empty;
/// <summary>
/// 付款方式
/// </summary>
[SugarColumn(ColumnDescription = "付款方式", Length = 200, IsNullable = false)]
public string PaymentMethod { get; set; } = string.Empty;
/// <summary>
/// 合作阶段编号
/// </summary>
[SugarColumn(ColumnDescription = "合作阶段编号", Length = 50, IsNullable = false)]
public string StageId { get; set; } = string.Empty;
/// <summary>
/// 纳税人识别号
/// </summary>
[SugarColumn(ColumnDescription = "纳税人识别号", Length = 100, IsNullable = false)]
public string TaxpayerId { get; set; } = string.Empty;
/// <summary>
/// 客户来源编号
/// </summary>
[SugarColumn(ColumnDescription = "客户来源编号", Length = 50, IsNullable = false)]
public string CustomerSourceId { get; set; } = string.Empty;
/// <summary>
/// 客户二级来源编号
/// </summary>
[SugarColumn(ColumnDescription = "客户二级来源编号", Length = 50, IsNullable = false)]
public string CustomerSecondSourceId { get; set; } = string.Empty;
/// <summary>
/// 客户等级编号
/// </summary>
[SugarColumn(ColumnDescription = "客户等级编号", Length = 50, IsNullable = false)]
public string CustomerGradeId { get; set; } = string.Empty;
/// <summary>
/// 销售人员
/// </summary>
[SugarColumn(ColumnDescription = "销售人员", Length = 50, IsNullable = false)]
public string SalesManId { get; set; } = string.Empty;
/// <summary>
/// 所属行业编号
/// </summary>
[SugarColumn(ColumnDescription = "所属行业编号", Length = 50, IsNullable = false)]
public string IndustryId { get; set; } = string.Empty;
/// <summary>
/// 省编号
/// </summary>
[SugarColumn(ColumnDescription = "省编号", Length = 50, IsNullable = false)]
public string ProvinceId { get; set; } = string.Empty;
/// <summary>
/// 市编号
/// </summary>
[SugarColumn(ColumnDescription = "市编号", Length = 50, IsNullable = false)]
public string CityId { get; set; } = string.Empty;
/// <summary>
/// 区编号
/// </summary>
[SugarColumn(ColumnDescription = "区编号", Length = 50, IsNullable = false)]
public string DistrictId { get; set; } = string.Empty;
}
[SugarTable("link_quotation")]
public class Quotation : Permission
{
/// <summary>
/// 报价日期
/// </summary>
[SugarColumn(ColumnDescription = "报价日期", IsNullable = false)]
public DateTime QuotationDate { get; set; } = DateTime.Now.Date;
/// <summary>
/// 交货日期
/// </summary>
[SugarColumn(ColumnDescription = "交货日期", IsNullable = false)]
public DateTime DeliveryDate { get; set; } = DateTime.Now.Date;
/// <summary>
/// 报价有效期
/// </summary>
[SugarColumn(ColumnDescription = "报价有效期", IsNullable = false)]
public DateTime ValidDate { get; set; } = DateTime.Now.Date;
/// <summary>
/// 客户编号
/// </summary>
[SugarColumn(ColumnDescription = "客户编号", Length = 50, IsNullable = false)]
public string CustomerId { get; set; } = string.Empty;
/// <summary>
/// 业务员编号
/// </summary>
[SugarColumn(ColumnDescription = "业务员编号", Length = 50, IsNullable = false)]
public string SalesManId { get; set; } = string.Empty;
/// <summary>
/// 公司编号
/// </summary>
[SugarColumn(ColumnDescription = "公司编号", Length = 50, IsNullable = false)]
public string CompanyId { get; set; } = string.Empty;
/// <summary>
/// 联系人
/// </summary>
[SugarColumn(ColumnDescription = "联系人", Length = 200, IsNullable = false)]
public string Contacts { get; set; } = string.Empty;
/// <summary>
/// 联系电话
/// </summary>
[SugarColumn(ColumnDescription = "联系电话", Length = 200, IsNullable = false)]
public string ContactsPhone { get; set; } = string.Empty;
/// <summary>
/// 客户地址
/// </summary>
[SugarColumn(ColumnDescription = "客户地址", Length = 200, IsNullable = false)]
public string CustomerAddress { get; set; } = string.Empty;
/// <summary>
/// 商机编号
/// </summary>
[SugarColumn(ColumnDescription = "商机编号", Length = 50, IsNullable = false)]
public string OpptyId { get; set; } = string.Empty;
/// <summary>
/// 备注
/// </summary>
[SugarColumn(ColumnDescription = "备注", Length = 1000, IsNullable = false)]
public string Remarks { get; set; } = string.Empty;
/// <summary>
/// 总金额
/// </summary>
[SugarColumn(ColumnDescription = "总金额", Length = 18, DecimalDigits = 2, IsNullable = false)]
public decimal SumAmount { get; set; } = 0;
/// <summary>
/// 是否成交
/// </summary>
[SugarColumn(ColumnDescription = "是否成交", Length = 1, IsNullable = false)]
public string DealOrNot { get; set; } = "N";
/// <summary>
/// 成交日期
/// </summary>
[SugarColumn(ColumnDescription = "成交日期", IsNullable = false)]
public DateTime DealOrNotDate { get; set; } = DateTime.MinValue.Date;
}
}
}第二种
using Newtonsoft.Json;
using SqlSugar;
namespace Test
{
public class Program
{
private static void Main(string[] args)
{
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = DbType.PostgreSQL,
ConnectionString = "PORT=5432;DATABASE=test4;HOST=localhost;PASSWORD=odoo;USERID=postgres",
IsAutoCloseConnection = true
});
db.CodeFirst.InitTables<Customer>();
db.CodeFirst.InitTables<Quotation>();
var today = DateTime.Now.Date;
var yesterday = today.AddDays(-1);
var firstDayOfMonth = new DateTime(today.Year, today.Month, 1);
var firstDayOfLastMonth = firstDayOfMonth.AddMonths(-1);
var firstDayOfYear = new DateTime(today.Year, 1, 1);
var firstDayOfLastYear = firstDayOfYear.AddYears(-1);
// Customer 数据
var customerInfo = db.Queryable<Customer>()
.Where(m => m.Active == "Y")
.Where(m => SqlFunc.MappingColumn<bool>(" ( \"m\".ownerusers && ARRAY['001'] ) OR ( NOT ( \"m\".relatedusers IS NULL OR \"m\".relatedusers = '{}' ) AND ( \"m\".relatedusers && ARRAY['001'] ) )"))
.Select(m => new
{
CustomerCount = SqlFunc.AggregateCount(m.Id),
TodayCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Date == today, 1, 0)),
YesterdayCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Date == yesterday, 1, 0)),
MonthCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Year == today.Year && m.CreateTime.Month == today.Month, 1, 0)),
LastMonthCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Year == firstDayOfLastMonth.Year && m.CreateTime.Month == firstDayOfLastMonth.Month, 1, 0)),
YearCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Year == today.Year, 1, 0)),
LastYearCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Year == firstDayOfLastYear.Year, 1, 0)),
//DealCustomerToday = SqlFunc.AggregateSum(SqlFunc.IIF(
// SqlFunc.Subqueryable<Quotation>()
// .Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Date == today)
// .Any(), 1, 0)),
//DealCustomerYesterday = SqlFunc.AggregateSum(SqlFunc.IIF(
// SqlFunc.Subqueryable<Quotation>()
// .Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Date == yesterday)
// .Any(), 1, 0)),
//DealCustomerMonth = SqlFunc.AggregateSum(SqlFunc.IIF(
// SqlFunc.Subqueryable<Quotation>()
// .Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" &&
// q.DealOrNotDate.Year == today.Year && q.DealOrNotDate.Month == today.Month)
// .Any(), 1, 0)),
//DealCustomerLastMonth = SqlFunc.AggregateSum(SqlFunc.IIF(
// SqlFunc.Subqueryable<Quotation>()
// .Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" &&
// q.DealOrNotDate.Year == firstDayOfLastMonth.Year && q.DealOrNotDate.Month == firstDayOfLastMonth.Month)
// .Any(), 1, 0)),
//DealCustomerYear = SqlFunc.AggregateSum(SqlFunc.IIF(
// SqlFunc.Subqueryable<Quotation>()
// .Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Year == today.Year)
// .Any(), 1, 0)),
//DealCustomerLastYear = SqlFunc.AggregateSum(SqlFunc.IIF(
// SqlFunc.Subqueryable<Quotation>()
// .Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Year == firstDayOfLastYear.Year)
// .Any(), 1, 0))
})
.First();
Console.WriteLine(JsonConvert.SerializeObject(customerInfo));
}
public class Permission
{
/// <summary>
/// 唯一编号
/// </summary>
[SugarColumn(ColumnDescription = "唯一编号", Length = 50, IsPrimaryKey = true, IsNullable = false)]
public string Id { get; set; } = string.Empty;
/// <summary>
/// 单据类型
/// </summary>
[SugarColumn(ColumnDescription = "单据类型", Length = 50, IsNullable = false)]
public string Type { get; set; } = string.Empty;
/// <summary>
/// 创建人
/// </summary>
[SugarColumn(ColumnDescription = "创建人", Length = 50, IsNullable = false)]
public string CreateUser { get; set; } = string.Empty;
/// <summary>
/// 创建人
/// </summary>
[SugarColumn(ColumnDescription = "创建时间", IsNullable = false)]
public DateTime CreateTime { get; set; } = DateTime.Now;
/// <summary>
/// 更新人
/// </summary>
[SugarColumn(ColumnDescription = "更新人", Length = 50, IsNullable = true)]
public string UpdateUser { get; set; } = string.Empty;
/// <summary>
/// 更新时间
/// </summary>
[SugarColumn(ColumnDescription = "更新时间", IsNullable = true)]
public DateTime UpdateTime { get; set; } = DateTime.Now;
/// <summary>
/// 归档标志
/// </summary>
[SugarColumn(ColumnDescription = "归档标志", Length = 1, IsNullable = false)]
public string Active { get; set; } = "Y";
/// <summary>
/// 部门关联
/// </summary>
[SugarColumn(ColumnDescription = "部门关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)]
public string[] RelatedDepartments { get; set; } = Array.Empty<string>();
/// <summary>
/// 用户关联
/// </summary>
[SugarColumn(ColumnDescription = "用户关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)]
public string[] RelatedUsers { get; set; } = Array.Empty<string>();
/// <summary>
/// 所有者
/// </summary>
[SugarColumn(ColumnDescription = "所有者", ColumnDataType = "text[]", IsArray = true, IsNullable = false)]
public string[] OwnerUsers { get; set; } = Array.Empty<string>();
}
[SugarTable("link_customer")]
public class Customer : Permission
{
/// <summary>
/// 客户编号
/// </summary>
[SugarColumn(ColumnDescription = "客户编号", Length = 50, IsNullable = false)]
public string CustomerId { get; set; } = string.Empty;
/// <summary>
/// 客户名称
/// </summary>
[SugarColumn(ColumnDescription = "客户名称", Length = 200, IsNullable = false)]
public string Name { get; set; } = string.Empty;
/// <summary>
/// 公司编号
/// </summary>
[SugarColumn(ColumnDescription = "公司编号", Length = 50, IsNullable = false)]
public string CompanyId { get; set; } = string.Empty;
/// <summary>
/// 部门编号
/// </summary>
[SugarColumn(ColumnDescription = "部门编号", Length = 50, IsNullable = false)]
public string DepartmentId { get; set; } = string.Empty;
/// <summary>
/// 付款方式
/// </summary>
[SugarColumn(ColumnDescription = "付款方式", Length = 200, IsNullable = false)]
public string PaymentMethod { get; set; } = string.Empty;
/// <summary>
/// 合作阶段编号
/// </summary>
[SugarColumn(ColumnDescription = "合作阶段编号", Length = 50, IsNullable = false)]
public string StageId { get; set; } = string.Empty;
/// <summary>
/// 纳税人识别号
/// </summary>
[SugarColumn(ColumnDescription = "纳税人识别号", Length = 100, IsNullable = false)]
public string TaxpayerId { get; set; } = string.Empty;
/// <summary>
/// 客户来源编号
/// </summary>
[SugarColumn(ColumnDescription = "客户来源编号", Length = 50, IsNullable = false)]
public string CustomerSourceId { get; set; } = string.Empty;
/// <summary>
/// 客户二级来源编号
/// </summary>
[SugarColumn(ColumnDescription = "客户二级来源编号", Length = 50, IsNullable = false)]
public string CustomerSecondSourceId { get; set; } = string.Empty;
/// <summary>
/// 客户等级编号
/// </summary>
[SugarColumn(ColumnDescription = "客户等级编号", Length = 50, IsNullable = false)]
public string CustomerGradeId { get; set; } = string.Empty;
/// <summary>
/// 销售人员
/// </summary>
[SugarColumn(ColumnDescription = "销售人员", Length = 50, IsNullable = false)]
public string SalesManId { get; set; } = string.Empty;
/// <summary>
/// 所属行业编号
/// </summary>
[SugarColumn(ColumnDescription = "所属行业编号", Length = 50, IsNullable = false)]
public string IndustryId { get; set; } = string.Empty;
/// <summary>
/// 省编号
/// </summary>
[SugarColumn(ColumnDescription = "省编号", Length = 50, IsNullable = false)]
public string ProvinceId { get; set; } = string.Empty;
/// <summary>
/// 市编号
/// </summary>
[SugarColumn(ColumnDescription = "市编号", Length = 50, IsNullable = false)]
public string CityId { get; set; } = string.Empty;
/// <summary>
/// 区编号
/// </summary>
[SugarColumn(ColumnDescription = "区编号", Length = 50, IsNullable = false)]
public string DistrictId { get; set; } = string.Empty;
}
[SugarTable("link_quotation")]
public class Quotation : Permission
{
/// <summary>
/// 报价日期
/// </summary>
[SugarColumn(ColumnDescription = "报价日期", IsNullable = false)]
public DateTime QuotationDate { get; set; } = DateTime.Now.Date;
/// <summary>
/// 交货日期
/// </summary>
[SugarColumn(ColumnDescription = "交货日期", IsNullable = false)]
public DateTime DeliveryDate { get; set; } = DateTime.Now.Date;
/// <summary>
/// 报价有效期
/// </summary>
[SugarColumn(ColumnDescription = "报价有效期", IsNullable = false)]
public DateTime ValidDate { get; set; } = DateTime.Now.Date;
/// <summary>
/// 客户编号
/// </summary>
[SugarColumn(ColumnDescription = "客户编号", Length = 50, IsNullable = false)]
public string CustomerId { get; set; } = string.Empty;
/// <summary>
/// 业务员编号
/// </summary>
[SugarColumn(ColumnDescription = "业务员编号", Length = 50, IsNullable = false)]
public string SalesManId { get; set; } = string.Empty;
/// <summary>
/// 公司编号
/// </summary>
[SugarColumn(ColumnDescription = "公司编号", Length = 50, IsNullable = false)]
public string CompanyId { get; set; } = string.Empty;
/// <summary>
/// 联系人
/// </summary>
[SugarColumn(ColumnDescription = "联系人", Length = 200, IsNullable = false)]
public string Contacts { get; set; } = string.Empty;
/// <summary>
/// 联系电话
/// </summary>
[SugarColumn(ColumnDescription = "联系电话", Length = 200, IsNullable = false)]
public string ContactsPhone { get; set; } = string.Empty;
/// <summary>
/// 客户地址
/// </summary>
[SugarColumn(ColumnDescription = "客户地址", Length = 200, IsNullable = false)]
public string CustomerAddress { get; set; } = string.Empty;
/// <summary>
/// 商机编号
/// </summary>
[SugarColumn(ColumnDescription = "商机编号", Length = 50, IsNullable = false)]
public string OpptyId { get; set; } = string.Empty;
/// <summary>
/// 备注
/// </summary>
[SugarColumn(ColumnDescription = "备注", Length = 1000, IsNullable = false)]
public string Remarks { get; set; } = string.Empty;
/// <summary>
/// 总金额
/// </summary>
[SugarColumn(ColumnDescription = "总金额", Length = 18, DecimalDigits = 2, IsNullable = false)]
public decimal SumAmount { get; set; } = 0;
/// <summary>
/// 是否成交
/// </summary>
[SugarColumn(ColumnDescription = "是否成交", Length = 1, IsNullable = false)]
public string DealOrNot { get; set; } = "N";
/// <summary>
/// 成交日期
/// </summary>
[SugarColumn(ColumnDescription = "成交日期", IsNullable = false)]
public DateTime DealOrNotDate { get; set; } = DateTime.MinValue.Date;
}
}
}这两种方式均会出现该问题
热忱回答（6）
-
fate sta VIP0
2周前把无用的SELECT中都删掉只提供出错的代码。
0 回复 -
fate sta VIP0
2周前
我这边看一下。
0 回复 -
=_= VIP0
2周前
@fate sta：
using Newtonsoft.Json; using SqlSugar; namespace Test { public class Program { private static void Main(string[] args) { SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.PostgreSQL, ConnectionString = "PORT=5432;DATABASE=test4;HOST=localhost;PASSWORD=odoo;USERID=postgres", IsAutoCloseConnection = true }); db.CodeFirst.InitTables<Customer>(); db.CodeFirst.InitTables<Quotation>(); var today = DateTime.Now.Date; var yesterday = today.AddDays(-1); // Customer 数据 var customerInfo = db.Queryable<Customer>() .Where(m => m.Active == "Y") .Where(m => SqlFunc.MappingColumn<bool>(" ( \"m\".ownerusers && ARRAY['001'] ) OR ( NOT ( \"m\".relatedusers IS NULL OR \"m\".relatedusers = '{}' ) AND ( \"m\".relatedusers && ARRAY['001'] ) )")) .Select(m => new { TodayCount = SqlFunc.AggregateSum(SqlFunc.IIF(m.CreateTime.Date == today, 1, 0)), //DealCustomerToday = SqlFunc.AggregateSum(SqlFunc.IIF( // SqlFunc.Subqueryable<Quotation>() // .Where(q => q.Active == "Y" && q.CustomerId == m.Id && q.DealOrNot == "Y" && q.DealOrNotDate.Date == today) // .Any(), 1, 0)), }) .First(); Console.WriteLine(JsonConvert.SerializeObject(customerInfo)); } public class Permission { /// <summary> /// 唯一编号 /// </summary> [SugarColumn(ColumnDescription = "唯一编号", Length = 50, IsPrimaryKey = true, IsNullable = false)] public string Id { get; set; } = string.Empty; /// <summary> /// 单据类型 /// </summary> [SugarColumn(ColumnDescription = "单据类型", Length = 50, IsNullable = false)] public string Type { get; set; } = string.Empty; /// <summary> /// 创建人 /// </summary> [SugarColumn(ColumnDescription = "创建人", Length = 50, IsNullable = false)] public string CreateUser { get; set; } = string.Empty; /// <summary> /// 创建人 /// </summary> [SugarColumn(ColumnDescription = "创建时间", IsNullable = false)] public DateTime CreateTime { get; set; } = DateTime.Now; /// <summary> /// 更新人 /// </summary> [SugarColumn(ColumnDescription = "更新人", Length = 50, IsNullable = true)] public string UpdateUser { get; set; } = string.Empty; /// <summary> /// 更新时间 /// </summary> [SugarColumn(ColumnDescription = "更新时间", IsNullable = true)] public DateTime UpdateTime { get; set; } = DateTime.Now; /// <summary> /// 归档标志 /// </summary> [SugarColumn(ColumnDescription = "归档标志", Length = 1, IsNullable = false)] public string Active { get; set; } = "Y"; /// <summary> /// 部门关联 /// </summary> [SugarColumn(ColumnDescription = "部门关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] RelatedDepartments { get; set; } = Array.Empty<string>(); /// <summary> /// 用户关联 /// </summary> [SugarColumn(ColumnDescription = "用户关联", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] RelatedUsers { get; set; } = Array.Empty<string>(); /// <summary> /// 所有者 /// </summary> [SugarColumn(ColumnDescription = "所有者", ColumnDataType = "text[]", IsArray = true, IsNullable = false)] public string[] OwnerUsers { get; set; } = Array.Empty<string>(); } [SugarTable("link_customer")] public class Customer : Permission { /// <summary> /// 客户编号 /// </summary> [SugarColumn(ColumnDescription = "客户编号", Length = 50, IsNullable = false)] public string CustomerId { get; set; } = string.Empty; /// <summary> /// 客户名称 /// </summary> [SugarColumn(ColumnDescription = "客户名称", Length = 200, IsNullable = false)] public string Name { get; set; } = string.Empty; /// <summary> /// 公司编号 /// </summary> [SugarColumn(ColumnDescription = "公司编号", Length = 50, IsNullable = false)] public string CompanyId { get; set; } = string.Empty; /// <summary> /// 部门编号 /// </summary> [SugarColumn(ColumnDescription = "部门编号", Length = 50, IsNullable = false)] public string DepartmentId { get; set; } = string.Empty; /// <summary> /// 付款方式 /// </summary> [SugarColumn(ColumnDescription = "付款方式", Length = 200, IsNullable = false)] public string PaymentMethod { get; set; } = string.Empty; /// <summary> /// 合作阶段编号 /// </summary> [SugarColumn(ColumnDescription = "合作阶段编号", Length = 50, IsNullable = false)] public string StageId { get; set; } = string.Empty; /// <summary> /// 纳税人识别号 /// </summary> [SugarColumn(ColumnDescription = "纳税人识别号", Length = 100, IsNullable = false)] public string TaxpayerId { get; set; } = string.Empty; /// <summary> /// 客户来源编号 /// </summary> [SugarColumn(ColumnDescription = "客户来源编号", Length = 50, IsNullable = false)] public string CustomerSourceId { get; set; } = string.Empty; /// <summary> /// 客户二级来源编号 /// </summary> [SugarColumn(ColumnDescription = "客户二级来源编号", Length = 50, IsNullable = false)] public string CustomerSecondSourceId { get; set; } = string.Empty; /// <summary> /// 客户等级编号 /// </summary> [SugarColumn(ColumnDescription = "客户等级编号", Length = 50, IsNullable = false)] public string CustomerGradeId { get; set; } = string.Empty; /// <summary> /// 销售人员 /// </summary> [SugarColumn(ColumnDescription = "销售人员", Length = 50, IsNullable = false)] public string SalesManId { get; set; } = string.Empty; /// <summary> /// 所属行业编号 /// </summary> [SugarColumn(ColumnDescription = "所属行业编号", Length = 50, IsNullable = false)] public string IndustryId { get; set; } = string.Empty; /// <summary> /// 省编号 /// </summary> [SugarColumn(ColumnDescription = "省编号", Length = 50, IsNullable = false)] public string ProvinceId { get; set; } = string.Empty; /// <summary> /// 市编号 /// </summary> [SugarColumn(ColumnDescription = "市编号", Length = 50, IsNullable = false)] public string CityId { get; set; } = string.Empty; /// <summary> /// 区编号 /// </summary> [SugarColumn(ColumnDescription = "区编号", Length = 50, IsNullable = false)] public string DistrictId { get; set; } = string.Empty; } [SugarTable("link_quotation")] public class Quotation : Permission { /// <summary> /// 报价日期 /// </summary> [SugarColumn(ColumnDescription = "报价日期", IsNullable = false)] public DateTime QuotationDate { get; set; } = DateTime.Now.Date; /// <summary> /// 交货日期 /// </summary> [SugarColumn(ColumnDescription = "交货日期", IsNullable = false)] public DateTime DeliveryDate { get; set; } = DateTime.Now.Date; /// <summary> /// 报价有效期 /// </summary> [SugarColumn(ColumnDescription = "报价有效期", IsNullable = false)] public DateTime ValidDate { get; set; } = DateTime.Now.Date; /// <summary> /// 客户编号 /// </summary> [SugarColumn(ColumnDescription = "客户编号", Length = 50, IsNullable = false)] public string CustomerId { get; set; } = string.Empty; /// <summary> /// 业务员编号 /// </summary> [SugarColumn(ColumnDescription = "业务员编号", Length = 50, IsNullable = false)] public string SalesManId { get; set; } = string.Empty; /// <summary> /// 公司编号 /// </summary> [SugarColumn(ColumnDescription = "公司编号", Length = 50, IsNullable = false)] public string CompanyId { get; set; } = string.Empty; /// <summary> /// 联系人 /// </summary> [SugarColumn(ColumnDescription = "联系人", Length = 200, IsNullable = false)] public string Contacts { get; set; } = string.Empty; /// <summary> /// 联系电话 /// </summary> [SugarColumn(ColumnDescription = "联系电话", Length = 200, IsNullable = false)] public string ContactsPhone { get; set; } = string.Empty; /// <summary> /// 客户地址 /// </summary> [SugarColumn(ColumnDescription = "客户地址", Length = 200, IsNullable = false)] public string CustomerAddress { get; set; } = string.Empty; /// <summary> /// 商机编号 /// </summary> [SugarColumn(ColumnDescription = "商机编号", Length = 50, IsNullable = false)] public string OpptyId { get; set; } = string.Empty; /// <summary> /// 备注 /// </summary> [SugarColumn(ColumnDescription = "备注", Length = 1000, IsNullable = false)] public string Remarks { get; set; } = string.Empty; /// <summary> /// 总金额 /// </summary> [SugarColumn(ColumnDescription = "总金额", Length = 18, DecimalDigits = 2, IsNullable = false)] public decimal SumAmount { get; set; } = 0; /// <summary> /// 是否成交 /// </summary> [SugarColumn(ColumnDescription = "是否成交", Length = 1, IsNullable = false)] public string DealOrNot { get; set; } = "N"; /// <summary> /// 成交日期 /// </summary> [SugarColumn(ColumnDescription = "成交日期", IsNullable = false)] public DateTime DealOrNotDate { get; set; } = DateTime.MinValue.Date; } } }差别不大，都是重复的
0 回复 -
=_= VIP0
2周前
@fate sta：大佬，咋样了
0 回复 -
fate sta VIP0
2周前
db.Queryable<Customer>("m")
你先指定个M这个我后面版本支持。
0 回复 -
=_= VIP0
2周前
@fate sta：好吧，要改好多地方了
0 回复