Entity Framework Core 6,选择具有可互换的Where子句。

huangapple go评论82阅读模式
英文:

Entity Framework Core 6, select with interchangable Where clause

问题

我正在将一些内联SQL代码转换为Entity Framework Core 6。整个应用程序是用C#编写的。现有代码中的一些SQL可以接受可选的 WHERE 子句。

一个简化的示例可能如下所示:

public List<DataObject> SelectWithWhere(string optionalWhere)
{    
    string sql = string.Format("SELECT * FROM SomeTable {0} ;", optionalWhere);
   
    // 调用数据库类以执行查询并格式化为列表(伪代码)
    List<DataObject> list = ExecuteSqlAndFormat(sql);
 
    return list;
}

我想要能够将条件传递给一个类似的使用Entity Framework Core 6的函数,但是一直没有找到任何关于如何传递条件或者代表条件的实体结构的明确示例。

public List<DataObject> SelectWithWhere(string optionalWhere)
{                
    List<DataObject> list = (from t in dbContext.SomeTable.Where(-- 在这里做什么? --)
                             select new DataObject
                             {
                                  // 从查询中填充数据成员
                             }
                             ).ToList<DataObject>()
     
    return list;
}

首选解决方案将仅使用Entity Framework Core 6中可用的内容,而不使用任何第三方附加组件或专有工具。

谢谢!

英文:

I'm converting some inline SQL code over to Entity Framework Core 6. The overall application is in C#. Some of the existing code has SQL that can take an optional WHERE clause.

A simplified example might look like this:

public List&lt;DataObject&gt; SelectWithWhere(string optionalWhere)
{    
    string sql = string.Format(&quot;SELECT * FROM SomeTable {0} ;&quot;, optionalWhere);
   
    // call to DB class to execute query and format into List (pseudo code)
    List&lt;DataObject&gt; list = ExecuteSqlAndFormat(sql);
 
    return list;
}

I'd like to be able to pass in Where criteria to a similar function that uses Entity Framework Core 6, but have not been able to find any good clear examples of passing a where clause, or some kind of entity structure that represents a where clause, into a method that can be then passed to EF Core.

public List&lt;DataObject&gt; SelectWithWhere(string optionalWhere)
{                
    List&lt;DataObject&gt; list = (from t in dbContext.SomeTable.Where(-- what to do here? --)
                             select new DataObject
                             {
                                  // fill in data members from query
                             }
                             ).ToList&lt;DataObject&gt;()
     
    return list;
}

The preferred solution would use only what is available in Entity Framework Core 6 and not any third party add-ons or proprietary tools.

Thanks!

答案1

得分: 1

以下是已翻译的内容:

public List<DataObject> SelectWithWhere(string optionalWhere)
{                
    List<DataObject> list = (from t in dbContext.SomeTable
        where t.SomeFieldName.Contains(optionalWhere)
        || t.SomeOtherFieldName.Contains(optionalWhere)
        select new DataObject
        {
            // 从查询中填充数据成员
        }
        ).ToList<DataObject>();
 
    return list;
}
英文:

NOTE: Below optionalWhere is a search value

public List&lt;DataObject&gt; SelectWithWhere(string optionalWhere)
{                
    List&lt;DataObject&gt; list = (from t in dbContext.SomeTable
        Where(t.SomeFieldName.Contains(optionalWhere)
        || t.SomeOtherFieldName.Contains(optionalWhere)
        select new DataObject
        {
            // fill in data members from query
        }
        ).ToList&lt;DataObject&gt;()
 
        return list;
    }

答案2

得分: 0

感谢@Jeff提供的链接,我能够组合一个将被整合到我的代码中的解决方案。下面是一个示例解决方案。

首先,假设已安装Entity Framework Core 6,并已生成了用于数据库的类和存储库。

要进行测试,只需将其中一个Where表达式传递给WhereTestMethod()。

// 用于测试动态Where子句的方法。
public string TestDynamicWhere()
{
	string retval = string.Empty;
	List<ResultListClass> resultListClass = null;
	int isActiveFilter = 1;
	string graded = "Graded";

	try
	{
		// 三种不同的Where条件进行测试:
		
		// 没有外部变量:
		Expression<Func<EF_ClassName, bool>> WhereTestFx = (EF_ClassName c) => c.isActive == true;

		// 一个外部变量:
		Expression<Func<EF_ClassName, bool>> WhereTestFx2 = (EF_ClassName c) => c.isActive == Convert.ToBoolean(isActiveFilter);

		// 两个外部变量:
		Expression<Func<EF_ClassName, bool>> WhereTestFx3 = (EF_ClassName c) => c.isActive == Convert.ToBoolean(isActiveFilter) &&
		                                                                       c.NameField.Contains(graded);

		resultListClass = WhereTestMethod(WhereTestFx3);

		retval = JsonConvert.SerializeObject(resultListClass, Newtonsoft.Json.Formatting.Indented);
	}
	catch (Exception ex)
	{
		retval = ex.Message;
	}

	return retval;
}

// 用于此示例测试结果的非存储库类
public class ResultListClass
{
	public string NameField { get; set; }
	public bool isActive { get; set; }
}

// 接受Where Func、执行查询并返回结果的方法
public List<ResultListClass> WhereTestMethod(Expression<Func<EF_ClassName, bool>> fx)
{
	List<ResultListClass> resultListClass = null;

	try
	{
		resultListClass = (from a in dbContext.EF_ClassName.Where(fx)
						   select (new ResultListClass
						   {
							  isActive = a.isActive,
							  NameField = a.NameField,
						   }
						   )).ToList<ResultListClass>();
	}
	catch(Exception ex) 
	{
		throw;
	}
	
	return resultListClass;
}

请注意,上述代码是用C#编写的,并包含一些用于测试动态Where子句的示例方法。

英文:

Thanks to the link proved by @Jeff, I was able to put together a solution that will be incorporated into my code. A sample solution is below.

First, assume that Entity Framework Core 6 is installed and has generated the classes and repositories for a DB.

To test, just pass one of the Where Expressions into the WhereTestMethod().

// Method to test the dynamic Where clauses.
public string TestDynamicWhere()
{
	string retval = string.Empty;
	List&lt;ResultListClass&gt; resultListClass = null;
	int isActiveFilter = 1;
	string graded = &quot;Graded&quot;;

	try
	{
		// three different where conditions to test:
		
        // no external variables:
	    Expression&lt;Func&lt;EF_ClassName, bool&gt;&gt; WhereTestFx = (EF_ClassName c) =&gt; c.isActive == true;
	
	   // one external variable:
	   Expression&lt;Func&lt;EF_ClassName, bool&gt;&gt; WhereTestFx2 = (EF_ClassName c) =&gt; c.isActive == Convert.ToBoolean(isActiveFilter);
	
	   // two external variables:
	   Expression&lt;Func&lt;EF_ClassName, bool&gt;&gt; WhereTestFx3 = (EF_ClassName c) =&gt; c.isActive == Convert.ToBoolean(isActiveFilter) &amp;&amp;
	                                                                           c.NameField.Contains(graded);

		resultListClass = WhereTestMethod(WhereTestFx3);

		retval = JsonConvert.SerializeObject(resultListClass, Newtonsoft.Json.Formatting.Indented);
	}
	catch (Exception ex)
	{
		retval = ex.Message;
	}

	return retval;

}

// non repository class to use for test results for this example
public class ResultListClass
{
	public string NameField { get; set; }
	public bool isActive { get; set; }
}

// method that takes a Where Func, executes the query, and returns a result
public List&lt;ResultListClass&gt; WhereTestMethod(Expression&lt;Func&lt;EF_ClassName, bool&gt;&gt; fx)
{
	List&lt;ResultListClass&gt; resultListClass = null;

	try
	{
		resultListClass = (from a in dbContext.EF_ClassName.Where(fx)
						   select (new ResultListClass
						   {
							  isActive = a.isActive,
							  NameField = a.NameField,
						   }
						   )).ToList&lt;ResultListClass&gt;();
	}
	catch(Exception ex) 
	{
		throw;
	}
	
	return resultListClass;
}

huangapple
  • 本文由 发表于 2023年7月18日 04:27:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/76707879.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定