CODEWITHSUNDEEP
c#linqentity-framework
Daybreaker
Daybreaker

Reputation: 1047

Return bool value if it exists in Another table

I have two tables. Appointment and Blacklist. When i blaklist a customer i put his cusId as a foreign key to blacklist table.

Appointment
------------
(AppId,Time,CusId)


Blacklist
-----------
(CusId,date)

Here the CusId is referenced to Customer . What i want is when i search with appointment id in the Appointment table, if the customer relevant to that appointment is in the blacklist table return true else false.

I am new to EF. Please help me to fix this matter. Thanks in advance.

Upvotes: 0

Views: 2527

Answers (3)

Habib
Habib

Reputation: 223352

int appId = 10; // appID to check

bool ifBlackListed = BlackList.Any(r=> r.CusID == 
                                       Appointment.First(t=> 
                                                         t.AppId == appId)
                                                  .CusId);

I am not sure what SQL query would be generated against it, but you can get the CustId from appointment first and then compare it in BlackList, that might save you from sending extra search query on each iteration of BlackList.Any like:

int custID = Appointment.First(t=> t.AppId == appId).CusId;
bool ifBlackListed = BlackList.Any(r=> r.CusId == custID);

Also if you are expecting null back from your first query (against Appointment) then use FirstOrDefault and check against null before accessing CusId

Upvotes: 2

p.s.w.g
p.s.w.g

Reputation: 149050

A query like this should work:

bool isBlacklisted = 
    (from a in dbContext.Appointments
     join b in dbContext.Blacklist on a.CusId equals b.CusId
     where a.AppId = appointmentId
     select b)
    .Any();

But if you have a navigation property set up between your Appointment and Blacklist entities, this is even simpler. You could do something like this:

bool isBlacklisted = 
    (from a in dbContext.Appointments
     where a.AppId = appointmentId
     select a.Blacklist.Any())
    .First();

Or possibly even this:

bool isBlacklisted = dbContext.Appointments.Find(appointmentId).Blacklist.Any();

Upvotes: 3

AD.Net
AD.Net

Reputation: 13409

If foreign keys and entities are setup properly:

from a in Appointment
//where a.CustId == customerId //if you want to pass a customer id
select new {Appointment = a, IsBlackListed = a.Customer.BlackList.Any())

else:

from a in Appointment
//where a.CustId == customerId //if you want to pass a customer id
select new {Appointment = a, IsBlackListed = BlackList.Any(b=>b.CustId == a.CustId)}

Upvotes: 0

Related Questions