Problem in converting sql query syntax to entityspaces syntax

Hi All,

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 wrong code in entity spaces.

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

Thanks

Hello,

No one here to help me................................

Hi,

We build LightSpeed, not Entity Spaces. If you need help with Entity Spaces I suggest you seek help from them or on Stack Overflow.

Kind regards,

John-Daniel Trask
Co-founder
Mindscape Limited

Thanks a lot