Entity SQL doesn't work if edmx file in another project

Question

I'm trying to use Enitity SQL to query data, but if the edmx file in another project, there will be an exception thrown. Below is my test steps.

Create a Class Library project and add an edmx file to it, create from database.
Create a Console Application, add the Class Library project to reference and copy the app.config file to this project.

Write the code as below

using (NorthwindEntities context = new NorthwindEntities())
{
    string queryString = @"SELECT VALUE cus 
                           FROM NorthwindEntities.Customers AS cus 
                           WHERE cus.ID > 10";
    ObjectQuery<Customers> cusQuery = 
        context.CreateQuery<Customers>(queryString);
    List<Customers> cusList = cusQuery.ToList();
}

When I run the Console Application project, an exception is thrown: "'ID' is not a member of type 'NorthwindModel.Customers' in the currently loaded schemas."

It seems the schema doesn't loaded into the project, anyone has ideas? Addional question: in this query, I select all the properties of this type, if I only select some of the properties, how to return an anonymous type of ObjectQuery?

Any suggestions are appreciate.

Have you checked that the generated `NorthwindModel.Customer` really has a full caps `ID` property? — nemesv, Jun 23 '12 at 05:41
Thanks for your quick reply. Yes, the edmx file is generated from database, all the properties are in the model. — James, Jun 23 '12 at 05:44
EF4.3.1, furthermore, I have tried to using self-tracking entities template, and create the models in another class library project by add the .tt files as link. the test project throws the same error — James, Jun 23 '12 at 05:47
The [Customer table](http://merc.tv/img/fig/Northwind_diagram.jpg) does not have an `ID` column but `CustomerId` so the generated `NorthwindModel.Customer` also does not have an `ID` property. Try with `CustomerId`! — nemesv, Jun 23 '12 at 05:57
Hi nemesv, I have modified some columns before, this property has modified as ID. I found a link which is similar with mine, http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/917ea5e4-25a3-4dcc-a8a5-fd52d0ac5c74/ — James, Jun 23 '12 at 06:01
I cannot repro your issue with the steps you have provided. It works fine as it should if the edmx in a different project. So the problem is somewhere around your not mentioned customizations. Try to repro yourself with your current steps, maybe you will figure out what is causing the problem. — nemesv, Jun 23 '12 at 06:08

score 0 · Answer 1 · answered Jun 23 '12 at 09:55

This is not an EF problem.

You have written the SQL statement by hand, you are not using the table definition that is in the EDMX file.

If you try to execute the SQL statement in the Query prompt of SQL Server, you will see that it fails there as well.

Try something like this:

var cus = from customers in context.Customers select customers;
var cusList = cus.ToList();

Entity SQL doesn't work if edmx file in another project

1 Answers1