Click here to Skip to main content
15,071,480 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi!

I have sql code that returns all appointment times that are not used on certain date

SQL
select terminRez
from TerminRezervacije t
where not exists(select TerminRezervacijeID
from Rezervacija as r where
t.TerminRezervacijeID=r.TerminRezervacijeID and  r.DatumRezervacije= '2021-01-14')


I managed to recreate it as query with c# LINQ

C#
var termin = from t in db.TerminRezervacije
                        where !db.Rezervacija.Where(r => t.TerminRezervacijeID == r.TerminRezervacijeID
                        && r.DatumRezervacije == mod.DatumRezervacije).Any()
                        select  t.terminRez;


I want to be able to display available appointment times with dropdown list.
Right now i have setup with SelectListItem that loads all appointment times from db
and cant figure out how to implement this linq query to be able to display results of query in my SelectListItem dropdown list.

C#
model.TerminRezervacije = db.TerminRezervacije
            .Select(p => new SelectListItem
            {
                Value = p.TerminRezervacijeID.ToString(),
                Text = p.terminRez


            }).ToList();


I appreciate any help!!

What I have tried:

I recreated sql query in linq code but cant figure out how to add query results to dropdown list
Posted
Updated 15-Jan-21 23:41pm

1 solution

Your query can be re-written this way:
C#
var termin = db.TerminRezervacije
    .Where(t=>  
        !db.Rezervacija.Any(r => t.TerminRezervacijeID == r.TerminRezervacijeID
            && r.DatumRezervacije == mod.DatumRezervacije))
    //.Select(t=> t)
    .ToList();


Then you need to bind the result of above query to DropDownList. For further details, please see:
Populate (Bind) DropDownList from ViewModel in ASP.Net MVC[^]
Create DropDownList using HtmlHelper in ASP.Net MVC[^]
Using the DropDownList Helper with ASP.NET MVC | Microsoft Docs[^]
   
Comments
sasko1 16-Jan-21 16:37pm
   
Thanks!!!
I somehow didnt see query you wrote and went looking through links.
Ended up writing code with SqlDataReader(first link) and it was exactly what i needed
Maciej Los 17-Jan-21 1:45am
   
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900