EntityCommand SQL Error

Question

I have a problem in my select statement in my query. This sql query works in SqlServer Management Studio and selects the requested data but in my class its gives me an error "The query syntax is not valid. Near term '*', line 9, column 47." then another error in my From Forums F clause thats after taking out LastPostBy, LastPostDate, LastPostTitle i also get an error in LastPostTitle but like I said it works in SQLServer Management Studio. I initally tried to do a "Exec sproc_Forums_GetForums" stored procedure and that didn't work this is my first attempt using this approach in with Entities. All my query is suppose to do is get a forum and display information from two tables named Forums and Posts

    public List<Forum> GetForums()
    {
        List<Forum> forums = null;
        using (EntityConnection conn = new EntityConnection("name=CMSEntities"))
        {
            conn.Open();
            string query = @"
                SELECT ForumGroup = (
                    CASE WHEN ParentID IS NOT NULL THEN
                        (SELECT Title FROM Forums WHERE ForumID = F.ParentID)           
                    ELSE
                        (SELECT Title FROM Forums WHERE ParentID IS NULL)
                    END),
                F.Title, F.Description, 
                ThreadCount = (SELECT COUNT(*) FROM Posts P WHERE P.ForumID = F.ForumID),
                LastPostBy = (SELECT TOP 1 AddedBy FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC), 
                LastPostDate = (SELECT TOP 1 AddedDate FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC),
                LastPostTitle = (SELECT TOP 1 Title FROM Posts P WHERE P.ForumID = F.ForumID ORDER BY P.PostID DESC)     
                FROM Forums F WHERE ParentID IS NOT NULL    
                ORDER BY Title
            ";
            EntityCommand cmd = new EntityCommand(query, conn);
            using (EntityDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
            {
                while (reader.Read())
                {
                    Forum forum = new Forum(
                        (int)reader["ForumID"],
                        "",
                        DateTime.Now,
                        reader["Title"].ToString(),
                        reader["Description"].ToString(),
                        0,
                        false,
                        null,
                        null,
                        null,
                        true,
                        reader["ForumGroup"].ToString(),
                        (int)reader["ThreadCount"],
                        reader["LastPostBy"].ToString(),
                        (DateTime)reader["LastPostDate"],
                        reader["LastPostTitle"].ToString());
                    forums.Add(forum);
                }
                return forums;
            }
        }
    }
}

here's a suggestion : use SQL Server Profiler to see what is the exact query that is sent to SQL Server . — Beatles1692
– Beatles1692, Commented Feb 5, 2012 at 8:45
how do you use SQL Server Profiler and where is it in VS2010 — ONYX
– ONYX, Commented Feb 5, 2012 at 9:00

Ladislav Mrnka · Accepted Answer · 2012-02-05 10:03:49Z

8

This is not valid ESQL query. You cannot make SQL query in your SQL management and execute it as ESQL. ESQL is different query language with its own syntax which is close to SQL but still have differences because it doesn't query database but entity model. For example simple query in ESQL looks like this:

SELECT VALUE Entity FROM Namespace.Entities AS Entity WHERE Entity.SomeField = 10

Do you see the difference to common SQL?

Instead of using ESQL and EntityCommand use either SqlCommand to execute SQL query or ObjectContext.ExecuteStoreQuery.

answered Feb 5, 2012 at 10:03

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EntityCommand SQL Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related