Freesql查询指定字段数据

返回指定字段:

//返回一个字段
List<int> t5 = fsql.Select<Topic>().ToList(a => a.Id);

//返回匿名类
List<匿名类> t6 = fsql.Select<Topic>().ToList(a => new { a.Id, a.Title });

//返回元组
List<(int, string)> t7 = fsql.Select<Topic>().ToList<(int, string)>("id, title");

//返回SQL字段
List<匿名类> t8 = fsql.Select<Topic>().ToList(a => new {
    a.Id,
    a.Title,
    a.Type, //可以直接返回导航属性 Type
    cstitle = "substr(a.title, 0, 2)", //将 substr(a.title, 0, 2) 作为查询字段
    csnow = Convert.ToDateTime("now()"), //将 now() 作为查询字段
    //奇思妙想:怎么查询开窗函数的结果
});

//返回子查询的字段
List<匿名类> t9 = fsql.Select<Topic>().ToList(a => new {
    a.Id,
    count = fsql.Select<T2>().Count(),
    max = fsql.Select<T2>().Max(b => b.Id),
    min = fsql.Select<T2>().Min(b => b.Id),
    name = fsql.Select<2>().First(b => b.name)
});

 

关于忽略某些字段的功能,因为多表机制不好确定,所以没提供官方接口,不过可以通过以下扩展方法来实现:

public static List<T1> ToListIgnore<T1>(this ISelect<T1> that, Expression<Func<T1, object>> selector)
{
    if (selector == null) return that.ToList();
    var s0p = that as Select0Provider;
    var tb = s0p._tables[0];
    var parmExp = tb.Parameter ?? Expression.Parameter(tb.Table.Type, tb.Alias);
    var initExps = tb.Table.Columns.Values
        .Where(a => a.Attribute.IsIgnore == false)
        .Select(a => new 
        {
            exp = Expression.Bind(tb.Table.Properties[a.CsName], Expression.MakeMemberAccess(parmExp, tb.Table.Properties[a.CsName])),
            ignored = TestMemberExpressionVisitor.IsExists(selector, Expression.MakeMemberAccess(parmExp, tb.Table.Properties[a.CsName]))
        })
        .Where(a => a.ignored == false)
        .Select(a => a.exp)
        .ToArray();
    var lambda = Expression.Lambda<Func<T1, T1>>(
        Expression.MemberInit(
            Expression.New(tb.Table.Type),
            initExps
        ),
        parmExp
    );
    return that.ToList(lambda);
}

class TestMemberExpressionVisitor : ExpressionVisitor
{
    public string MemberExpString;
    public bool Result { get; private set; }

    public static bool IsExists(Expression selector, Expression memberExp)
    {
        var visitor = new TestMemberExpressionVisitor { MemberExpString = memberExp.ToString() };
        visitor.Visit(selector);
        return visitor.Result;
    }
    protected override Expression VisitMember(MemberExpression node)
    {
        if (!Result && node.ToString() == MemberExpString) Result = true;
        return node;
    }
}

 

参考网址: https://www.cnblogs.com/FreeSql/p/11531376.html

 

Freesql查询指定字段数据

上一篇:【Database-Mysql】一键执行查询拼接的语句


下一篇:Oracle-同义词失效能否自动编译?