Linq-to-SQL complex custom object from SP

Question

I have a Linq-to-SQL model that uses stored procs in some places to return the objects where more complex SQL is required. This all works fine.

I now need to return a custom object from an SP that also encapsulates a Linq-to-SQL object. For example, I have a class called Employee based on an Employee table. I also have a custom class called rota defined as follows:

public class rota
{
    public Employee employee{ get; set; }
    public int DisplayOrder { get; set; }
    public DateTime StartingTime { get; set; }
    public DateTime FinishTime { get; set; }
}

I have some fairly complex linq that calculates an employees rota for any given day and then returns that object. I want to move this logic to a stored procedure so I have total control over the sql (the generated sql is not great) however I am not sure how to return that object?

Why don't you just return the Employee PK column(s) in place of the employee complext type and just have a standard anonymous type coming back from the procedure via LINQ to SQL? You could then manually populate the full Employee via a second query that returned employees and manually merge the two. It would only be a few lines of code. — JohnOpincar
– JohnOpincar, Commented Jan 28, 2011 at 13:56
@JohnOpincar - in this case this was what I did, thanks. However if I had a large number of results this would probably not be the way I went about it, do you know if it is possible to achieve what I was asking? Either way, if you post an answer I'll accept it — Macros
– Macros, Commented Jan 28, 2011 at 17:00

JohnOpincar · Accepted Answer · 2011-01-28 19:37:03Z

1

I believe this will work although I have not run it. If this does not, I know you could do this with a SQL function that returned a table variable.

from x in dataContext.StoredProc("", "", "")
select new Rota {
    DisplayOrder = x.DisplayOrder,
    StartingTime = x.StartingTime,
    FinishTime = x.FinishTime,
    Employee = new Employee {
        EmployeeId = x.EmployeeId,
        Name = x.EmployeeName
    }
}

answered Jan 28, 2011 at 19:37

JohnOpincar

5,8333 gold badges39 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brandon · Accepted Answer · 2011-12-16 19:53:40Z

1

I would suggest creating a constructor for the Rota object that takes the sproc result as a parameter... that way you can do

.Select(x => new Rota(x)).ToList<Rota>();

edited Dec 16, 2011 at 19:53

Brandon

70.3k32 gold badges201 silver badges227 bronze badges

answered May 26, 2011 at 18:28

Shibbz

2491 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

Linq-to-SQL complex custom object from SP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related