是否可以测试给定的表达式是否可以转换为SQL *，而不会连接到SQL数据库？ [英] Is it possible to test whether a given expression can be converted to SQL *without* connecting to a SQL database?

问题描述

例如： -

  //这个将被转换为SQL没有问题
表达式< Func< Foo，bool>>>谓词= x => x.Name =Foo; 
 
 //这个将抛出一个NotSupportedException，因为QueryProvider 
 //不支持引用比较
表达式< Func< Foo，bool>>谓词= x => x == someOtherFoo; 
 
 //这个不起作用，因为查询提供者不能
 // handle IsAwesome（）
表达式< Func< Foo，bool>>谓词= x => x.IsAwesome（）; 
  

我正在寻找一种在运行时间之前测试这种方法，理想情况下，数据库。

我花了一些时间拖网通过MSDN试图找到如何实例化我自己的QueryProvider，但是我的Google-fu似乎在今天失败了。

提前感谢

解决方案

你需要一个模型来做到这一点，但你不需要数据库。您可以将模型创建为EDMX，但使用Code First可能更容易。为了确保创建或使用Code First模型不需要数据库连接，您需要提供Code First通常从数据库获取的一些信息。本贴 http://blog.oneunicorn.com/2012/ 04/21 / code-first-building-blocks / 显示了如何使用DbModelBuilder来执行此操作以及如何从模型创建DbContext。你会得到一些这样的代码：

  var modelBuilder = new DbModelBuilder（）; 
 modelBuilder.Entity< Foo>（）; 
 var model = modelBuilder.Build（
 new DbProviderInfo（System.Data.SqlClient，2008））。 
  

您可能希望缓存模型对象，而不是为每个不同的测试重新创建它。 / p>

您还应禁用数据库初始化设置，以防止DbContext尝试连接到数据库。例如，在使用上下文之前，使用这样的调用：

  Database.SetInitializer&FooContext>（null）; 
  

现在，您只需在任何查询中使用ToString即可查看将生成的SQL。如果LINQ无法处理表达式，那么您将收到异常。例如，这将打印查询：

  using（var context = new FooContext（model））
 {
表达式< Func< Foo，bool>>>谓词= x => x.Name ==Foo; 
 
 Console.WriteLine（context.Foos.Where（predicate））; 
} 
  

这将抛出：

使用（var context = new FooContext（model））
{
var someOtherFoo = new Foo（）;

  
表达式< Func< Foo，bool>>>谓词= x => x == someOtherFoo; 
 
 Console.WriteLine（context.Foos.Where（predicate））; 
} 
  

显然，如果你正在编写测试，你不会只是打印到控制台，而是执行某种断言/检查。

For example:-

// This one will be converted to SQL no problem
Expression<Func<Foo, bool>> predicate = x => x.Name = "Foo";

// This one will throw a NotSupportedException because the QueryProvider
// doesn't support reference comparisons
Expression<Func<Foo, bool>> predicate = x => x == someOtherFoo;

// This one doesn't work because the query provider can't
// handle IsAwesome()
Expression<Func<Foo, bool>> predicate = x => x.IsAwesome();

I'm looking for a way to test this before runtime, ideally in an automated test isolated from the database.

I spent some time trawling through MSDN trying to find how to instantiate my own QueryProvider, but my Google-fu appears to be failing me today.

Thanks in advance!

解决方案

You need a model to do this, but you don't need a database. You can create the model as an EDMX, but it's probably easier to use Code First. To ensure that the creating or using the Code First model doesn't need a database connection you will need to provide some information that Code First normally obtains from the database. This post http://blog.oneunicorn.com/2012/04/21/code-first-building-blocks/ shows how to use the DbModelBuilder to do this and how to create a DbContext from the model. You'll end up with some code like this:

var modelBuilder = new DbModelBuilder();
modelBuilder.Entity<Foo>();
var model = modelBuilder.Build(
                new DbProviderInfo("System.Data.SqlClient", "2008")).Compile();

You'll probably want to cache the model object rather than create it again for every different test.

You should also disable database initializers to prevent DbContext from attempting to connect to the database. For example, before using the context make a call like this:

Database.SetInitializer<FooContext>(null);

Now you can just use ToString on any query to see what SQL it will generate. If LINQ can't handle the expression then you will get an exception. For example, this will print the query:

using (var context = new FooContext(model))
{
    Expression<Func<Foo, bool>> predicate = x => x.Name == "Foo";

    Console.WriteLine(context.Foos.Where(predicate));
}

This will throw:

using (var context = new FooContext(model))
{
    var someOtherFoo = new Foo();
    Expression<Func<Foo, bool>> predicate = x => x == someOtherFoo;

    Console.WriteLine(context.Foos.Where(predicate));
}

Obviously if you're writing tests you won't just be printing to the console, but rather performing some kind of assertions/checks.

这篇关于是否可以测试给定的表达式是否可以转换为SQL *，而不会连接到SQL数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！