Look at the following three tables. One: Trainee Info, two: Batch and three: Attendance.
Trainee join with batch by BatchId and then count attendance of trainee's from attendance table which required cross apply. Though I can finish it in the following way:
select
A.Id,
A.TraineeName,
B.BatchNo,
AttendanceCount=(Select AttendanceCount=Count(*) from @TA2 A_A where A.Id=A_A.TraineeId)
from @TA1 A
inner join @TB1 B on A.BatchId=B.Id
But here I would like to use cross apply. Following sql code working well in Sql Server.
DECLARE @TA1 TABLE
(
Id int,
BatchId int,
TraineeName varchar(50)
)
DECLARE @TB1 TABLE
(
Id int,
BatchNo varchar(50)
)
DECLARE @TA2 TABLE
(
Id int,
TraineeId varchar(50),
AttendanceDate datetime
)
insert into @TB1 values(1,'C#-B1')
insert into @TB1 values(2,'J2EE-B1')
insert into @TB1 values(3,'Networking-B1')
insert into @TA1 values(1,1,'AAA')
insert into @TA1 values(2,1,'ABC')
insert into @TA1 values(3,2,'ADC')
insert into @TA1 values(4,3,'AEB')
insert into @TA2 values(1,1,getdate())
insert into @TA2 values(1,2,getdate())
insert into @TA2 values(1,1,getdate())
insert into @TA2 values(1,2,getdate())
insert into @TA2 values(1,3,getdate())
select
A.Id,
A.TraineeName,
B.BatchNo,
C.AttendanceCount
from @TA1 A
inner join @TB1 B on A.BatchId=B.Id
cross apply (Select AttendanceCount=Count(*) from @TA2 A_A where A.Id=A_A.TraineeId) C
Come to linq to entitiy where cross apply is working fine while table doesn't return scalar value.
var query = from a1 in context.TA1
join b1 in context.TB1 on a1.BatchId equals b1.Id
from a2 in context.TA2
Where(a2.TraineeId == a.Id)
select new
{
a1.Id,
a1.TraineeName,
b1.BatchNo,
a2.AttendanceDate
};
but if I want to Count instead of a2.AttendanceDate what shall I do?
var query = from a1 in context.TA1
join b1 in context.TB1 on a1.BatchId equals b1.Id
from a2 in context.TA2
.Where(x => x.TraineeId == a.Id).Count()
select new
{
a1.Id,
a1.TraineeName,
b1.BatchNo,
a2.AttendanceDate
};
Thanks
Asif