Save Entity Framework Linq Query to database

Question

I was wondering if we can convert a Linq Query on the Entity Framework and save the query to the database by converting it to an Expression Tree and Serializing. Can someone please help me on this and point me in a right direction whether this can be done or not. Any help is greatly appreciated on this.

Thanks, Ajay.

esskar · Accepted Answer · 2012-09-22T10:21:09.267

3

i released a library for that purpose just yesterday. Serialize.Linq. It serializes linq expressions to xml, json or binary.

using System.Linq.Expressions
using Serialize.Linq.Extensions;

Expression<Func<Person, bool>> query = p => p.LastName == "Miller" 
    && p.FirstName.StartsWith("M");

Console.WriteLine(query.ToJson());
Console.WriteLine(query.ToXml());

edited Sep 22 '12 at 10:21

answered Sep 22 '12 at 10:15

esskar

10,638
3
36
57

can you convert the serialized query back to expression sequence? – Syam Oct 23 '12 at 23:17
yes, you can. see http://blog.esskar.de/articles/2012/9/serialize_linq_expressions_over_wcf.html for an example – esskar Oct 24 '12 at 09:47
@esskar, the link is dead now. Can you post an answer that contains your code? – toddmo Dec 29 '14 at 19:13

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

You could turn the query into a string and then save the string.

This is from an answer by Nick Berardi:

var result = from x in appEntities
         where x.id = 32
         select x;

var sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

The sql generated by the query could be stored and re-used.

edited May 23 '17 at 12:32

Community

1
1

answered Jul 09 '12 at 23:41

Travis J

81,153
41
202
273

sorry for not asking it clearly. Basically i was thinking to save the linq predicate as a string into the database and read the value to run the linq predicate at runtime. Please let me know if there is a way to do that or if there is better approach? – ajay Aug 06 '12 at 19:45
Hello, what is the code to turn the trace string back into a working query? I like your answer because it does not have to be typed according to the query result's type. – toddmo Dec 29 '14 at 19:08
@toddmo - Back into a working query? The `result` is the query. Getting the trace string just shows what was queried. So if you wanted the results of the query, you would just look at `result` and probably ignore the trace string as it is more for analysis and debugging than it is useful for production. – Travis J Dec 29 '14 at 19:10
@TravisJ, No I mean, an in-memory Linq query expression. In this example, turn it back into `result`, in memory. As in, serialize it, then de-serialize it. – toddmo Dec 29 '14 at 19:21
@toddmo - You wouldn't need to serialize the string, you could just use it as it stood. `var storedResult = context.MyTableName.SqlQuery(sql); ` – Travis J Dec 29 '14 at 19:26
@TravisJ, only if I was using this trivial example code, which I'm not. I have basically the same need as the OP, to serialize and deserialize a linq query. So in other words, this can't be used for serialization / deserialization as per the OP's question, correct? – toddmo Dec 29 '14 at 19:27
@toddmo - There is no reason to serialize a string. So long as this is used to query the same structure it was generated for it will work. Using this process you can store the query string in your database to use later, but serializing the expression tree would be needlessly excessive. If you are serializing expression trees and storing them then perhaps you should revisit the design of that feature. – Travis J Dec 29 '14 at 19:29
@toddmo - If you want to give some more details on your exact situation, why don't you join the [c# chat room](http://chat.stackoverflow.com/rooms/7/c) ? – Travis J Dec 29 '14 at 19:30
@TravisJ, I think the disconnect here is that you are assuming I'm using EF, which I'm not. My linq query is Linq to Objects and I want to store and reuse that in memory linq to objects query. The selected answer was more exactly to this point but unfortunately the given link is a dead link. – toddmo Dec 29 '14 at 19:31
@toddmo - Perhaps you should ask a separate question to address that. There are many nuanced differences in linq2entity, linq2sql, and linq2object – Travis J Dec 29 '14 at 19:33

JwJosefy · Answer 3 · 2015-06-18T00:49:22.583

Use Sprint.Filter.OData. It converts a Func<T,bool> into string and back to code.

Sample:

public class TestSprintOData
{
    public static void Run()
    {
        // Parse a Func into string
        var query = Filter.Serialize<User>(u => u.IsActive && u.Email.Contains("@gmail.com"));

        // It'll generate the string "IsActive and substringof('@gmail.com', Email)"

        // Convert back to Expression, perhaps on server
        var query2 = Filter.Deserialize<User>(query);

        // Compiles to Func, so you can use as delegate to Where
        var f = query2.Compile();

        var list = new List<User>
        {
            new User{Name="Johnny", IsActive = true, Email = "johnny@gmail.com"},
            new User{Name="abc", IsActive = false, Email = ""},
            new User{Name="dude", IsActive=true, Email = "dude@gmail.com"}
        };

        var result = list.Where(f);            
    }
}

class User
{
    public string Name;
    public string Phone;
    public string Login;
    public string Email;
    public bool IsActive;
}

You can also use it as a Nuget Package

score 0 · Answer 4 · answered Mar 06 '14 at 18:45

0

You may want to consider using Entity SQL rather than LINQ in this case. Entity SQL is a string query that works against your EF conceptual model rather than directly against the database.

answered Mar 06 '14 at 18:45

Jim Wooley

10,169
1
25
43

Save Entity Framework Linq Query to database

4 Answers4

Linked

Related