在EF框架中,有时候涉及到多个表查询,分组查询等一些复杂的sql语句的时候,写linq和lambda就相对有一点复杂了,要是没有一些linq和lambda的功底可能就很费事了,接下来将介绍如何用原始的sql语句来进行数据查询!
使用不带参数的原始的sql查询
using (NO1DBContext ctx = new NO1DBContext())
{
sql="select * from 'tc_organization'"
var ContractInformation = ctx.Database.SqlQuery<ContractDTO>(sql).ToList();
return ContractInformation;
}
以上tolist()是必不可少的!
使用带参数的原始的sql语句查询
using (NO1DBContext ctx = new NO1DBContext())
{
var userName=new MySqlParameter("@name", name);
sql="select * from 'tc_organization' where organization_name = @name"
var ContractInformation = ctx.Database.SqlQuery<ContractDTO>(sql,userName).ToList();
return ContractInformation;
}
以上的name是通过方法传递的参数
复杂的join连接sql语句 以及分组查询
sql = @"SELECT contract_year,ou.organization_id,substring(organization_name, 5, 3) as organization,au.user_name,contract_name,ct.contract_copy_url,sc.contract_url as sc_contract_copy_url,sc.check_state as sc_check_state FROM `tc_organization` o LEFT JOIN tc_organization_user ou ON o.id = ou.organization_id LEFT JOIN tc_allusers au ON ou.user_id = au.id LEFT JOIN to_contract ct ON au.id = ct.student_id LEFT JOIN to_screen sc ON au.id = sc.student_id WHERE o.company_id = '23VAJAVAXT492QKFsfUBru' AND o.p_id = 'NV5onqoiU3Pmf8yojccaCk'AND organization_name<>'中学部' AND organization_name<>'暂停使用人员' ";
sql=sql+"GROUP BY au.id"
var ContractInformation = ctx.Database.SqlQuery<ContractDTO>(sql).ToList();
return ContractInformation;
以上就是原始的sql语句查询用法,这里的sql语句都是字符串,复杂的语句大家通过sql软件自己测好!