Problem with Entity SQL

Question

I am learning Entity Framework 4 from book.

And I try to make some Query with Entity SQL like this :

using (var context = new BAEntities())
{                
    string str = "SELECT VALUE c " +
                 "FROM BAEntities.Contacts " +
                 "AS c " +
                 "WHERE c IS NOT OF(BAModel.Customer)";

    ObjectQuery<Contact> qry = context.CreateQuery<Contact>(str);

    Console.WriteLine(qry.Count());
}

My query purpose is to take all Objects of Contact Type but not of Customer Type. Where, Customer inherits from Contact

But I got the following error :

Type 'BAModel.Customer' could not be found. Make sure that the required schemas are loaded and that the namespaces are imported correctly. Near type name, line 1, column 64.

But, If I do query with LINQ to Entities like this :

ObjectQuery<Contact> qry = context.Contacts.Where(c => !(c is Customer));

then program can Run correctly.

So, why in Entity SQL BAModel.Customer is not found, whereas I ran the code within the same project in which the model is located.

Please help me in this.

Thanks in Advance.

Unfortunately, comparing an EF query vs. your adhoc SQL isn't a good comparison. The EF query knows how to construct the SQL query as expected by the RDBMS, without chance for syntax errors. — p.campbell, Jun 08 '11 at 14:01
I put the comparison just to clarify that the program can run correctly. I just confused why with Entity SQL EF couldn't found the Model. — adaapanya, Jun 08 '11 at 17:38

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

Suggest using an EntityCommand instead of ObjectContext.CreateQuery.

Try this:

using (EntityConnection conn = new EntityConnection("name=MyEntities"))
{
    string str = "SELECT VALUE c " +
                 "FROM BAEntities.Contacts " +
                 "AS c " +
                 "WHERE c IS NOT OF(BAModel.Customer)";
    using (EntityCommand cmd = new EntityCommand(str, conn))
    {                    
        using (var reader = cmd.ExecuteReader())
        {                        
            while (reader.Read())
            {
                Console.WriteLine(reader["c"]);
            }
        }
    }
}

This similar question/answer has the root of the problem:

If your query is executed with the EntityCommand, the data type is an EDM type. If the query is executed with ObjectQuery, the data type is a CLR type.

edited May 23 '17 at 11:48

Community

1
1

answered Jun 08 '11 at 13:34

p.campbell

98,673
67
256
322

IS NOT OF TYPE is not valid Entity SQL sintax. – adaapanya Jun 08 '11 at 14:18
@ada: What happened when you get the trace string. Confirm you're using Oracle? – p.campbell Jun 08 '11 at 15:02
Exception occured before ToTraceString(), so I didn't get the string. I use SQL Server – adaapanya Jun 08 '11 at 15:22
Thanks for your help, I have found the solution. See my Answer. But, once again, Thanks. – adaapanya Jun 09 '11 at 04:32

adaapanya · Answer 2 · 2011-06-09T04:41:29.087

0

It turns out I use the wrong Namespace. The right fullname is BAGA.Customer not BAModel.Customer. The right namespace is the namespace of the classes that was generated from Model (the namespace of the classes within Model1.Designer.cs), not the namespace of the Model.

edited Jun 09 '11 at 04:41

answered Jun 09 '11 at 04:31

adaapanya

91
4

score 0 · Answer 3 · edited Oct 24 '11 at 06:42

0

Try this:

_ObjectContext.MetadataWorkspace.LoadFromAssembly(Assembly.GetAssembly(typeof(BAModel.Customer)));

before running CreateQuery command. hope this helps.

edited Oct 24 '11 at 06:42

Awais Qarni

17,492
24
75
137

answered Oct 24 '11 at 02:21

Aran

81
1
1

Problem with Entity SQL

3 Answers3