Convert linq expression to sql server query
I am using some crm framework and this framework has no internal orm and does not use entity structure, only simple sql queries.
I have an Entity for every table in the database. So I have, for example:
public class Customer{
public string FirstName{get;set;}
public int Status{get;set;}
}
Is there anyway I can write linq queries and convert them to sql without using entity framework or NHibernate ? I'm looking for something like.
IQueryable linq = from LinqProvider.Get<Customer>() int customer where customer.FirstName == "test" and Status > 1;
string sqlQuery = LinqProvider.ToSqlQuery(linq);
//Select * from Customer where FirstName = "test" and Status > 1
I would live to have some advanced features like combining sorting and aggregation functions.
source to share
Note the difference between the following two lines (linq in lambda form):
var dataQ = Customer.Where(o=>(o.FirstName == "test" && o.Status > 1);
var dataL = Customer.Where(o=>(o.FirstName == "test" && o.Status > 1).ToList();
var dataS = Customer.SingleOrDefault(o=>(o.FirstName == "test" && o.Status > 1);
As far as I know, linq queries are converted to lamba and then optimized and automatically compiled (from framework 4.5). By default your database context should be lazy loading and concurrency optimized. Lazy loading means no data is received before you really need it. In this case, .ToList()
and SingleOrDefault
will force the data to try again. This means they will appear in the Entity Framework Profiler .
If you don't want to use it or cannot, you can use 'ToTraceString', but it doesn't work for dataL
or dataS
because these are not requests, but specific instances.
File.AppendAllText(traceFile, ((ObjectQuery)dataQ).ToTraceString());
return dataQ.ToList();
Edit
The assumptions I made:
- My guess is that you cannot write your own SQL, but are familiar with Linq.
- You have a fancy database that a third party provider will use, or you can't even do it.
- Have you (manually?) Created a mapping for the database tables
You can now use the code first approach . You are creating a database from these classes. Then you query it and get SQL . I assumed this was clear. Note that you may also want to migrate your code with the first migration, because you will most likely have to make changes.
Examples (Google only):
- http://msdn.microsoft.com/en-us/data/jj193542.aspx
- http://msdn.microsoft.com/en-us/data/jj591621.aspx
Edit 2
Made an example: https://gist.github.com/margusmartsepp/f9fcc9178600ca53acf6
[Table("CustomerTest")]
public class Customer
{
[Key]
public int Id { get; set; }
public string FirstName { get; set; }
public int Status { get; set; }
}
public class CustomerContext : DbContext
{
public CustomerContext(): base("name=Program.CustomerContext"){}
public DbSet<Customer> Customers { get; set; }
}
//PM> Install-Package EntityFramework
//PM> Install-Package EntityFramework.SqlServerCompact
static void Main(string[] args)
{
using (var db = new CustomerContext())
{
var item = new Customer {FirstName = "test", Status = 2};
db.Customers.Add(item);
db.SaveChanges();
var items = db.Customers.Where(o => (o.FirstName == "test" && o.Status > 1));
Console.WriteLine(items.ToString());
}
Console.ReadKey();
}
Output example:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[Status] AS [Status]
FROM [CustomerTest] AS [Extent1]
WHERE (N'test' = [Extent1].[FirstName]) AND ([Extent1].[Status] > 1)
source to share