Unable to disting the record in Link Expression

Question

I have two tables: TblAppointmentTypes and TblEmployeeInfo. There is many to many relation between these two tables with a joined table that is TblEmployeeServices. There are two records in TblAppointmentTypes and four records in TblEmployeeInfo. All the two records of TblAppointmentTypes is assigned to all of the records in TblEmployeeInfo, that there is eight records in TblEmployeeServices. I want to retrive all the services that are assigned to all the four Employees, it should return 2 that is assigned to all the four employees, but my query return 8 records, four duplicate services.

I am using Telerik Open Access ORM. Here is the code:

public static List<TblAppointmentType> GetAllAppointmentType(List<int> employeeIDs)
{
    var list = new List<TblAppointmentType>();
    if (employeeIDs != null && employeeIDs.Count > 0)
    {
        var dc = new Entities();
        list = (from a in dc.TblAppointmentTypes.Distinct()
               join e in dc.TblEmployeeServices on a.ID equals e.AppointmentTypeID
               where a.IsDeleted == false && employeeIDs.Contains<int>(e.EmployeeID)
               select a).ToList();
     }
     return list;
 }

score 0 · Answer 1 · answered Apr 29 '13 at 07:48

0

As I figured it out, You want to get all AppointmentTypes which are related to all Employees. It means that AppTypes that are related to 3 Employees (in your case) should be excluded. So, the most efficient code that I have tried is like this :

list = (from a in dc.TblAppointmentTypes
               join e in dc.TblEmployeeServices on a.ID equals e.AppointmentTypeID
               where a.IsDeleted == false 
               group e.EmployeeID by a into g
               where g.count() == employeeIDs.Count()
               select g.Key).ToList();

answered Apr 29 '13 at 07:48

vahid shavikloo

1
1

sorry for late reply, but this code did not worked for me, it throws this exception: An exception occured during the execution of 'Extent().Join(Extent(), a => a.ID, e => e.AppointmentTypeID, (a, e) => new <>f__AnonymousType8`2(a = a, e = e)).Where(<>h__TransparentIdentifier2e => (<>h__TransparentIdentifier2e.a.IsDeleted == False)).GroupBy – Waqas Ahmed May 07 '13 at 10:10

Unable to disting the record in Link Expression

1 Answers1