c# - Linq to Entities Query Optimisation -

c# - Linq to Entities Query Optimisation -

i've got linq query select ticket based on whether satisfies few conditions. query runs fine , works okay, i'm worried increment in number of possible returns noticeably worse goes.

could suggest ways improve or optimise query run faster/better? doing horribly wrong against standard practice?

here's query. i've swapped names. i'm thinking select's in select new statement problem well.

var attentionobj = (from c in context.supportticketentities c.statusid != 3 && ((c.isallocated == false) // not allocated || (c.flaggedforassist == true && c.allocatedtoemployeeid == empid) // flagged assist || ((from d in c.supportticketdatas c.allocatedtoemployeeid == empid && c.fogbugzattachments.count == 0 orderby d.timestamp descending select d.timestamp).firstordefault() < warningdt) // ticket not replied || ((from e in c.supportticketdatas c.allocatedtoemployeeid == empid orderby e.timestamp descending select e).firstordefault().raisedbyuser == true) // client has replied || (c.isescalated == true)) // ticket escalated select new { refid = c.refid, ticketid = c.supportticketid, clientcompany = c.clientdata.company, isallocated = c.isallocated, datelogged = c.datelogged, isflagged = c.flaggedforassist, needsreply = (from d in c.supportticketdatas orderby d.timestamp descending select d.timestamp).firstordefault() < warningdt, custreply = (from e in c.supportticketdatas orderby e.timestamp descending select e).firstordefault().raisedbyuser == true, isescalated = c.isescalated }).toarray();

edit: i've created stored procedure, although i'm not best @ sql. okay?

@empid int, @warningdt datetime select * supporttickets statusid != 3 , isallocated = 0 union select * supporttickets statusid != 3 , flaggedforassist = 1 , allocatedtoemployeeid = @empid union select * supporttickets statusid != 3 , allocatedtoemployeeid = @empid , not exists (select fogbugzlinkid fogbugzattachment) , (select max(timestamp) supportticketdata supporttickets.supportticketid = supportticketdata.supportticketid) < @warningdt union select supporttickets.* supporttickets bring together supportticketdata on supporttickets.supportticketid = supportticketdata.supportticketid supporttickets.statusid != 3 , supporttickets.allocatedtoemployeeid = @empid , supportticketdata.timestamp = (select max(timestamp) supportticketdata supporttickets.supportticketid = supportticketdata.supportticketid) , supportticketdata.raisedbyuser = 1 union select * supporttickets statusid != 3 , supporttickets.isescalated = 1

so appears using stored procedure 1 in initial statement much much more efficient using hideous linq or statement.

i'll mark reply gets closed. @ original question sproc.

c# sql linq entity-framework linq-to-entities