对Linq的SQL查询使用方法语法

Can anyone convert this sql query to Linq Method Syntax:

任何人都可以将此SQL查询转换为Linq方法语法:

select isnull(upl_data, pol_defaultdata),
  upl_userid, upl_countryid, 
  pol_sadmModuleid, pol_name, 
  pol_namelabel,pol_datatype 
from sadmpolicy
left join userpolicy on upl_policyid = pol_id
where pol_scope = 3

2 个解决方案

#1

You can do this:

你可以这样做:

var results = from l in sadmpolicyList
              join r in userpolicyList.Where(up=>up.pol_scope =3).ToList() on l.upl_policyid equals r.pol_id into G
from t in G.DefaultIfEmpty()
select new
{
  polData = l.upl_data ?? r.pol_defaultdata,
  upl_userid = l.upl_userid, 
  ...
};

(But I am not sure which column is coming from which table, so you might need to adjust the aliases.)

(但我不确定哪个列来自哪个表,因此您可能需要调整别名。)

#2

-1

Further to the answer above you could break it out a little and do something along the lines of:

除了上面的答案,你可以稍微分解一下,并做一些事情:

private string prepareSQLCondition(List<paramofsomekind> parameterList, number returnVal) { 

string condition = "SELECT ISNULL (upl_data, pol_defaultdata)";

int count = 1;
// Traverse params
foreach (var param in parameterList)
{
    if (parameterList.Count == count)
    {
        condition += "," + param.value + ",";
    }
    else
    {
       condition += "," + param.value + ",";
    }

    count++;
}

return condition + "WHERE pol_scope =" + returnVal;
}

From here you could add more layers of functionality, apply some more control flow, perhaps cater for certain exceptions until you get a fully dynamic solution.

从这里,您可以添加更多功能层,应用更多控制流,或许可以满足某些异常,直到您获得完全动态的解决方案。

You can then call your method using: prepareSQLCondition("param1, param2, param3..., 3); where the string representation of the params is your <List>.

然后,您可以使用:prepareSQLCondition(“param1,param2,param3 ...,3)调用您的方法;其中params的字符串表示形式是您的。

#1