convert sql syntax to entityspaces syntax

Question

I am newbie in entityspaces and trying to convert my sql query into entityspaces format

I have 3 tables

Course
Lesson
Attemps

I also have a view 'v_Course' and I am using this view in the query

Below is my sql query:

Select c.*,
   sub.res
FROM  (
  SELECT l.[CourseID],
     Count(l.CourseID) as res
  FROM   [Lesson] l
      INNER JOIN [Attempt] a
      ON (l.[LessonID] = a.[LessonID]
      AND l.[CourseID] IN (SELECT DISTINCT c.[CourseID]
                           FROM   [Course] c
                          )) group by l.CourseID
 ) AS sub  INNER JOIN v_Course c ON c.CourseID=sub.CourseID order by res desc

This query returns me the desired results successfully in SQL Query Browser

Now I want to convert this syntax in entity spaces and below is the code

//Query for the join

AttemptQuery aq = new AttemptQuery("a");

//SubQuery of course id
CourseQuery cq = new CourseQuery("c");
cq.es.Distinct = true;
cq.Select(cq.CourseID);

LessonQuery lq = new LessonQuery("l");
lq.Select(lq.CourseID, lq.CourseID.Count().As("res"));
lq.InnerJoin(aq).On(lq.LessonID == aq.LessonID & lq.CourseID.In(cq));

lq.GroupBy(lq.CourseID);
lq.Load();

VCourseCollection vCourseColl = new VCourseCollection();
vCourseColl.Query.SelectAllExcept(vCourseColl.Query.CourseWriterName);
    vCourseColl.Query.Where(vCourseColl.Query.CourseIsDeleted.Equal(false),vCourseColl.Query.CourseIsActive.Equal(true), vCourseColl.Query.ActiveLessonCount.GreaterThan(0), vCourseColl.Query.CourseCategoryID.NotEqual(7));
vCourseColl.Query.OrderBy(vCourseColl.Query.CourseName.Ascending);
vCourseColl.Query.From(lq).As("sub");
vCourseColl.Query.InnerJoin(lq).On(vCourseColl.Query.CourseID==lq.CourseID);
vCourseColl.Query.Load();

But when I run this page I get this kind of errors

SqlException was unhandled by the user code

Ambiguous column name 'CourseID'.

Invalid column name 'CourseCategoryID'.

Ambiguous column name 'CourseID'.

Invalid column name 'CourseName'.

Invalid column name 'CourseDescription'.

Invalid column name 'CourseIsDeleted'.

Invalid column name 'CourseImageID'.

Invalid column name 'CourseKeyWords'.

Invalid column name 'CourseIsActive'.

Invalid column name 'OutstandingIssues'.

Invalid column name 'CourseWriterEmployeeID'.

Invalid column name 'CourseAnimatorEmployeeID'.

Invalid column name 'CourseAnimatorName'.

Invalid column name 'TrainingTime'.

Invalid column name 'CourseCategoryName'.

Invalid column name 'SafetyImageID'.

Invalid column name 'FeedbackRatingAverage'.

Invalid column name 'CourseCategorySortOrder'.

Invalid column name 'TotalLessonCount'.

Invalid column name 'TotalLessonsInProduction'.

Invalid column name 'ActiveLessonCount'.

Invalid column name 'TrainingQuestionsTime'.

Invalid column name 'TrainingLessonsTime'.

I am sure I am writing the worng code in entity spaces.

Anyone here to help me please...................

Thanks

(Note: I have asked the same question earlier but with poor formatted code so asking it again with clean code)

No one is here to help me ................. – Sobia Idrees Sep 06 '13 at 17:17 — Sobia Idrees, Sep 06 '13 at 17:17

score 1 · Answer 1 · answered Nov 04 '13 at 12:16

See http://brewdawg.github.io/Tiraggo.EF/ (it has the same syntax)

And also

http://www.entityspaces.net/www.entityspaces.net/blog/2011/11/02/EntitySpaces%20Dynamic%20Query%20Showcase%20Repost.aspx.html

One mistake you are making is calling lq.Load(), you never load directly via a query, also, don't use the built in query in your collection, use a query object, this at the end call Load(yourQuery) on your collection, if you don't get it post back and I'll help you write the query, but look at those samples I posted.

convert sql syntax to entityspaces syntax

1 Answers1