none
How to write following sub-select query in linq to entities RRS feed

  • Question

  • Hi!

    I'm struggling translating following sql to linq to entities:

    	SELECT z.*
    	FROM (
    		SELECT 
    			z.ID,
    			case
    				when z.zadatakStatusID = 20 then 10 -- zatvoren zadatak
    				when z.zadatakStatusID <> 20 and datediff(day, z.datumPocetak, GETDATE()) > 0 then 20 -- otvoreni zadatak koji je u kašnjenju
    				when z.zadatakStatusID <> 20 and DATEDIFF(day, getdate(), z.datumPocetak) > @brojDana then 40 -- nadolazeći zadatak
    				else 30 -- redovni zadatak
    			end tipZadatka
    		FROM dbo.ugovor u
    		inner join ugovorGrupa ug on u.ID = ug.ugovorID
    		inner join slucaj s on ug.ID = s.ugovorGrupaID
    		inner join zadatak z on s.ID = z.slucajID
    		where
    			(u.ID = @ugovorID or (@ugovorID is null and u.id in (select ugovorID from dbo.UgovoriKojeDjelatnikVidi(@djelatnikId))))
    			and (ug.Id = @ugovorGrupaId or @ugovorGrupaId is null)
    			and (
    				(@tipIspisa = 0 and z.djelatnikId = @djelatnikId)
    				or
    				(@tipIspisa = -10 and z.djelatnikId is null)
    				or
    				(@tipIspisa = -20)
    			)
    	) q	
    	inner join zadatak z on q.ID = z.ID
    	where
    		(@zatvoreni = 1 or (@zatvoreni = 0 and q.tipZadatka <> 10))
    		and (@kasne = 1 or (@kasne = 0 and q.tipZadatka <> 20))
    		and (@nadolazeci = 1 or (@nadolazeci = 0 and q.tipZadatka <> 40))
    	order by q.datumPocetak
    


    So, basically, I have inner query which retrieves all IDs from zadatak table and calculates tipZadatka bases on some critera. Then outer select picks those IDs with other criteria based on inner tipZadatka, joins back to zadatak table and takes all fields (I'd like to get back entities of type zadatak). 

    As for inner table, ugovor (1..Many) ugovorGrupa (1..Many) slucaj (1..Many) zadatak (with corresponding navigation properties in Entity Framework)
    First clause in inner query is either selecting single record from ugovor or if @ugovorID is null selects all records from ugovor for which user has access to (which is returned by UgovorKojeDjelatnikVidi table-value function). In C# I have corresponding list which holds same list of entities of type ugovor (lstUgovori) to which current user has access to.  

    Any ideas if this SQL can be written in Linq2Entities?

    I'm using DbContext from Entity Framework 4.1

    Thanks a lot, 

    Mario

    Monday, December 5, 2011 9:35 AM

Answers

All replies