none
Complex Linq Query RRS feed

  • Question

  • Hi all, 

    I am new on linq but i love this abstraction, my problem is for very complex queries.

    I still begin to try my query in SQL before trying the same in Linq.

    My query :

    SELECT
    	sg.sgrp_id,
    	s.srvc_label,
    	su.sub_id,
    	SUM(CASE WHEN sgp.sgrp_IsVisible = 'Y' THEN 1 ELSE 0 END) AS Yes,
    	SUM(CASE WHEN sgp.sgrp_IsVisible = 'N' THEN 1 ELSE 0 END) AS No
    FROM
    	ServiceGroup as sg,
    	Service as s,
    	Subscription as su,
    	ServiceGroup as sgp
    WHERE
    	su.user_id = '12345'
    	AND su.srvc_id = s.srvc_id
    	AND s.sgrp_id = sg.sgrp_id
    	AND sg.sgrp_id LIKE sgp.sgrp_id + '%'
    GROUP BY
    	sg.sgrp_id,
    	s.srvc_label,
    	su.sub_id
    HAVING SUM(CASE WHEN sgp.sgrp_IsVisible = 'N' THEN 1 ELSE 0 END) = 0
    ORDER BY
    	s.srvc_label
    

    ServiceGroup represents a hierarchical tree.

    The id of this table is the list of its parents a itself separate by a ':'.

    I want services belong to service groups which not have a parent with attribute is_visible = 'N' (which not have a invisible parent).

    This query is exactly what i want but i am not able to do the same in Linq, because the join between sg and sgp in not based on 'equals' and i don't find how i can handle this kind of join.

    If someone has an idea...

    THX

    Thursday, September 13, 2012 8:37 AM

Answers

  • Thanks Dorado,

    i was able to do the linq query :

    justhave done something like :

    from sg in db.servicegroup

    from sgp in db.servicegroup

    where sg.id.startswith(sgp.id)

    group sg by sgp in g

    where g.count(gr => gr.sgp.isvisible == "N") == 0

    And it's OK.

    Thanks, finally, linq is not difficult... (I find the having statment easier in linq than sql ;))

    • Marked as answer by Brahim OUALI Friday, September 14, 2012 11:14 AM
    Friday, September 14, 2012 11:14 AM

All replies

  • I always suggest people who want to convert a complex worked T-SQL to Linq to Entities to execute the T-SQL directly in Entity Framework, or write it as a stored procedure and execute it. Because for now, Entity Framework couldn't convert Linq to an optimal T-SQL, if the query is simple, we can write a Linq to Entities, the performance reduce can be tolerated, but if the query is too complex, we convert the worked T-SQL to Linq, when EF try to convert the Linq back, it will generate a more complex T-SQL, some times, the performance reduce couldn't be tolerated. Even EF5 has improved the T-SQL generation, but execute it directly still a best solution for now.


    Go go Doraemon!

    Friday, September 14, 2012 1:44 AM
  • Thanks Dorado,

    i was able to do the linq query :

    justhave done something like :

    from sg in db.servicegroup

    from sgp in db.servicegroup

    where sg.id.startswith(sgp.id)

    group sg by sgp in g

    where g.count(gr => gr.sgp.isvisible == "N") == 0

    And it's OK.

    Thanks, finally, linq is not difficult... (I find the having statment easier in linq than sql ;))

    • Marked as answer by Brahim OUALI Friday, September 14, 2012 11:14 AM
    Friday, September 14, 2012 11:14 AM