LINQ to Entities Select New

Question

public static object ExecuteScalar(string SQL)
{
    try
    {
        var A = new EGModel.EGEntity().Connection;

        var command = ((EntityConnection)(A)).StoreConnection.CreateCommand();
        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = SQL;
        if (((EntityConnection)(A)).StoreConnection.State == System.Data.ConnectionState.Closed)
            ((EntityConnection)(A)).StoreConnection.Open();

        return command.ExecuteScalar();
    }
    catch { return null; }
}

public object MFICHE(int ID)
        {
            var i = from b in IConnection.EGEntity().fiche
                    where (m.ID== ID)
                    select new { b.Date, m.Name, Addresss = IConnection.ExecuteScalar("SELECT main.F_ADDRESS(4588)") };

            return i;
        }

I am getting error:

LINQ to Entities does not recognize the method 'System.Object ExecuteScalar(System.String)' method, and this method cannot be translated into a store expression. Why i am getting error?

But Addresss = "ASASAS" is runing?

possible duplicate of: http://stackoverflow.com/questions/6998957/how-to-call-scalar-valued-function-from-linq-to-entities-server-side — Akhil, Jul 14 '12 at 07:40
I don't think it's legal to return anonymous objects ... you need to do processing inside the method — Chen Kinnrot, Jul 14 '12 at 08:05
Have you tried "SELECT main.F_ADDRESS(4588)" query directly in db . Does it work? — yo chauhan, Jul 14 '12 at 08:29

Jon Skeet · Answer 1 · 2012-07-14T08:22:28.017

The problem is that the expression tree generated from your query includes a call to your ExecuteScalar method - which the Entity Framework expression parser doesn't know anything about. It doesn't look inside that method to see what it's doing - it just knows that the call exists, and fails because it can't translate it.

You wouldn't normally want to execute a separate SQL statement for each result returned from a query? You've got an obvious "N+1 selects" problem.

If you know you've only got a single result (due to the ID constraint) you could fetch the relevant data into an object and then execute the second query:

public object MFICHE(int ID)
{
    var query = from b in IConnection.EGEntity().fiche
                where b.ID == ID
                select new { b.Date, b.Name };
    // You only expect a single result, right?
    var result = query.Single();
    // Shouldn't this be using something to do with the result?
    var address = IConnection.ExecuteScalar("SELECT main.F_ADDRESS(4588)");
    return new { result.Date, result.Name, Address = address };
}

As an aside, it's very odd to have static methods in a type beginning with I, which would usually be an interface. Additionally, this code:

catch { return null; }

is horrible - you should catch specific exceptions, log them, and normally rethrow them. It's almost never appropriate to just carry on as if nothing had gone wrong.

Hi JonSkeet, why i am getting error "Data source is an invalid type. It must be either an IListSource, IEnumerable, or IDataSource." — , Jul 14 '12 at 09:12
@cnrars: Well you haven't shown what you're *doing* with the result, or where you're getting that exception. If you're binding to this in a list view or something like that, you'd want to put the result in a list - but it's odd to bind *a single value* in a list like that. — Jon Skeet, Jul 14 '12 at 09:40

LINQ to Entities Select New

1 Answers1