-3

please i need your help on this How can i interpret this sql to linq:

SELECT  FK_ClassId, LectureDays
FROM tbl_TimeTables
WHERE (FK_ClassId = 11)
group by fk_classid, lecturedays
having count(distinct Period) < 7

Any help will be much appreciated thanks Tim

Ayodele Timothy
  • 53
  • 7
  • See if this helps you, at your own risk :) : [SQL to LINQ Tool](https://stackoverflow.com/questions/296972/sql-to-linq-tool) – Brien Foss Jan 15 '18 at 01:31
  • Thanks Brien for your reply, unfortunately my Linqer trial period has expired, and i don't have the means of buying the paid version, any other suggestions please. – Ayodele Timothy Jan 15 '18 at 01:44

2 Answers2

0

should be something along these lines (group by id, count period, apply where clause, project result)

var query = from timetable in context.TimeTables
            where timetable.ClassId == classId
            group timetable by new
            {
                timetable.ClassId,
                timetable.Period
            } into groupings
            let counter = groupings
                .Select(p => p.Period)
                .Distinct()
                .Count()
            where counter < 7
            select new
            {
                ClassId = groupings.Key.ClassId,
                Days = from grouping in groupings
                       select grouping.LectureDays
            };
Dan Dohotaru
  • 2,809
  • 19
  • 15
  • Hi Dan, thanks for your reply, please where do i check for WHERE (FK_ClassId = 11). Thanks – Ayodele Timothy Jan 15 '18 at 10:20
  • ideally you should pass it as a criteria to your function using the query but that depends a lot on how you want to consume the query. In the meantime I've updated my answer – Dan Dohotaru Jan 15 '18 at 10:36
  • Hi Dan, thanks for your reply, actually the result i was expecting should normally give ClassId = 11 and Days = 5, according to the sql query i posted, but its giving me a series of answers, but grouping them by the classid which is 11, i.e when i use linqpad – Ayodele Timothy Jan 15 '18 at 10:43
  • i am afraid i don't fully understand. does my answer resolve your question in the end? – Dan Dohotaru Jan 15 '18 at 10:45
  • Hi Dan, thanks for your help, i would have loved to show you a picture of what i'm trying to explain, so that you can understand fully. Meanwhile, is there no straight way of implementing my sql query? – Ayodele Timothy Jan 15 '18 at 11:12
  • Tim? I've translated your sql query into a linq query equivalent. If this does not help to resolve your overall problem it certainly should resolve the problem you've described in the question. I don't see how i can help you if you don't elaborate your underlying issues – Dan Dohotaru Jan 15 '18 at 11:20
  • feel free to edit your question and add a relevant image if that would help describing the issue better https://meta.stackexchange.com/questions/75491/how-to-upload-an-image-to-a-post – Dan Dohotaru Jan 15 '18 at 11:22
0

thanks for pointing me in the right direction, sorry i didn't reply for a while now, been so much tied up. After looking at your solution, i had time to play with it, and this below did it for me:

from t in Tbl_TimeTables 
where t.FK_ClassId == 11
        group t by new
        {
            t.FK_ClassId,
            t.LectureDays
        } into groupings
        let counter = groupings
            .Select(p => p.Period)
            .Distinct()
            .Count()
        where counter < 7
        select new
        {
            ClassId = groupings.Key.FK_ClassId,
            Days = (from grouping in groupings
                   select grouping.LectureDays).Distinct()
        }

I just had to change the group by key to lecturedays instead of period, which removed the series of results i was getting to just one result. Thanks very much Dan

Ayodele Timothy
  • 53
  • 7