多个where语句，如果变量不为空

Question

我希望查询从返回WANOM_DETAILS & STRDT tables where PIPELINE = 'F' AND HISTORICAL_FLAG <> 'T' AND INSTALLATION = 'EA' AND ANOMALY_CLASS = 'Critical'。

if语句工作正常，但目前查询只返回从WANOM_DETAILS & STRDT tables where ANOMALY_CLASS = 'Critical'，因为这是model.ToList();

之前的最后一行

任何帮助都将非常感激。下面是代码;

        strdt = "EA";
        anom_class = "Critical";
        var models = db.WANOM_DETAILS;
        var model = models.Where(wanom_details => wanom_details.STRDT.PIPELINE == "F");
        model = models.Where(wanom_details => wanom_details.STRDT.HISTORICAL_FLAG != "T");
        if (strdt != "")
        {
            model = models.Where(wanom_details => wanom_details.INSTALLATION == strdt);
        }
        if (anom_class != "")
        {
            model = models.Where(wanom_details => wanom_details.ANOMALY_CLASS == anom_class);
        }
        if (subdt != "")
        {
            model = models.Where(wanom_details => wanom_details.WANOM.SUBSTRUCTURE == subdt);
        }
        if (comp != "")
        {
            model = models.Where(wanom_details => wanom_details.WANOM.TYPE + "-" + wanom_details.WANOM.IDENT == comp);
        }
        return PartialView("_SiteAnomalyGridViewPartial", model.ToList());

Answer 1

可以链接Where的结果，所以:

X.Where(x => x.Prop > 1 && x.Prop2 == "1")

您可以实现相同的结果连接两个Where:

X.Where(x => x.Prop > 1).Where(x.Prop2 == "1").

你可以这样写:

strdt = "EA";
anom_class = "Critical";
var models = db.WANOM_DETAILS;
models = models.Where(wanom_details => wanom_details.STRDT.PIPELINE == "F");
models = models.Where(wanom_details => wanom_details.STRDT.HISTORICAL_FLAG != "T");
if (strdt != "")
{
    models = models.Where(wanom_details => wanom_details.INSTALLATION == strdt);
}
if (anom_class != "")
{
    models = models.Where(wanom_details => wanom_details.ANOMALY_CLASS == anom_class);
}
if (subdt != "")
{
    models = models.Where(wanom_details => wanom_details.WANOM.SUBSTRUCTURE == subdt);
}
if (comp != "")
{
    models = models.Where(wanom_details => wanom_details.WANOM.TYPE + "-" + wanom_details.WANOM.IDENT == comp);
}
return PartialView("_SiteAnomalyGridViewPartial", models.ToList());

编辑:

当使用&&操作符链条件的长Where表达式时，根据And的条件生成sql。但是，当您将IQueryable与某些Where表达式链接在一起时，linq-to-sql将为每个表达式生成单独的select-where，并将它们全部包装在Union查询中。db-server可能会优化，但对我来说感觉很尴尬。要获得第一个结果，可以使用great predicatebuilder扩展方法:

strdt = "EA";
anom_class = "Critical";
var predicate = PredicateBuilder.False<ClassOfWANOM_DETAILS>();
predicate.And(wanom_details => wanom_details.STRDT.PIPELINE == "F");
predicate.And(wanom_details => wanom_details.STRDT.HISTORICAL_FLAG != "T");
if (strdt != "")
    predicate.And(wanom_details => wanom_details.INSTALLATION == strdt);
if (anom_class != "")
    predicate.And(wanom_details => wanom_details.ANOMALY_CLASS == anom_class);
if (subdt != "")
    predicate.And(wanom_details => wanom_details.WANOM.SUBSTRUCTURE == subdt);
if (comp != "")
    predicate.And(wanom_details => wanom_details.WANOM.TYPE + "-" + wanom_details.WANOM.IDENT == comp);
return PartialView("_SiteAnomalyGridViewPartial", db.WANOM_DETAILS.Where(predicate).ToList());