LINQ to Entities Select New

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?

Answers


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.


Need Your Help

Synchronizing Database Schemas among Developers

database cakephp

I'm working on a project with couple others. We all have local copies of the project, which is also constantly updated via svn repo.