نوشتن کوئری Linq حاوی Join

[WindowsXp]

سلام دوستان
چطور میشه با استفاده از LINQ یک کوئری نوشت که نتیجه ی اجراش مثل کد SQL زیر باشه :

SELECT P.[PackageId]
,P.[PackageTitle]
,P.[Content]
,(
CASE
WHEN DATEDIFF(hour,P.[LastUpdate],V.[IssueDate]) IS NULL THEN 'No Download'
WHEN DATEDIFF(hour,P.[LastUpdate],V.[IssueDate]) >= 0 THEN 'Downloaded'
WHEN DATEDIFF(hour,P.[LastUpdate],V.[IssueDate]) < 0 THEN 'Redownload'
END
) AS Diff
FROM PackageVisits AS V
RIGHT OUTER JOIN Packages AS P
ON P.PackageId = V.Package_PackageId AND V.User_UserId=@Userid

[parvizwpf]

بابا دست خوش شما اینو تبدیل کنید خودتون تیکه تیکه، لینکو یاد میگیری.

[amir3321]

اگه مشکلت با case when هست از این نمونه ها استفاده کن

select QueueItem, COUNT(*) as [Count], SUM(CASE WHEN Queued = 0 THEN 1 ELSE 0 END) AS [Sent], SUM(CASE WHEN Queued = 1 THEN 1 ELSE 0 END) AS Queued, SUM(CASE WHEN Success = 1 THEN 1 ELSE 0 END) AS Exported, SUM(CASE WHEN Success = 0 THEN 1 ELSE 0 END) AS Failed from ExportQueue x group by QueueItem


تبدیل به لینک 1



var query = Context.ExportQueues.Select(x => new { QueueItem = x.QueueItem, Sent = !x.Queued ? 1 : 0, Queued = x.Queued ? 1 : 0, Exported = x.Success ? 1 : 0, Failed = !x.Success ? 1 : 0 }) .GroupBy(x => x.QueueItem) .Select(g => new { QueueItem = g.Key, Sent = g.Sum(x => x.Sent), Queued = g.Sum(x => x.Queued), Exported = g.Sum(x => x.Exported), Failed = g.Sum(x => x.Failed) }).ToList();


تبدیل به لینک 2

var query = Context.ExportQueues .GroupBy(x => x.QueueItem) .Select(g => new { QueueItem = g.Key, Sent = g.Sum(x => !x.Queued ? 1 : 0), Queued = g.Sum(x => x.Queued ? 1 : 0), Exported = g.Sum(x => x.Success ? 1 : 0), Failed = g.Sum(x => !x.Success ? 1 : 0 ) }).ToList();


اگه مشکلت با توابعی مثل DATEDIFF میتونی از توابع sqlfunction در

System.Data.Objects.SqlClient.SqlFunctions.DateDif fHour

استفاده کنی
یا از توابع EntityFanction در لینک زیر استفاده کنی

لینک (http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.aspx)

[oliya24]

www.learnfiles.com (http://www.learnfiles.com/)