Return List From Joined Tables Using LINQ

Liagapi

I am trying to return a list from three tables which have been joined using LINQ. At the moment I can only get a list which contains data from one table instead of all three tables. Below is my code

public List GetProductDetails(string productId)
{
var db = new ProductContext();
IEnumerable emps = db.Employees.ToList();
IEnumerable projs= db.Projects.ToList();
IEnumerable prods = db.Products.ToList();

var productionDetails = from e in emps
join prj in projs on e.EmployeeID equals prj.ProjectID
join prd in prods on e.EmployeeID equals prd.productID
where prd.productID == Convert.ToInt32(productId)
select new {employee = e.EmployeeName, project = prj.ProjectName , product = prd.ProductName };

return productionDetails.Select(m=>m.EmployeeName).ToList();
}

I would like the list to contain employee, project, and product.

Garth J Lancaster

my first worries are :-

public List<string> GetProductDetails(string productId)

and

return productionDetails.Select(m=>m.EmployeeName).ToList();

why ? I would (because I understand it if I do it this way) a) define a 'prodDetails' POCO and use that instead of 'string' as the return list type, b) do a select 'new prodDetails {employee = e.EmployeeName .... instead of using an anonymous type c) remove the .Select in the return productionDetails clause

Liagapi

Hi, thank you so much for your response. Your solution would work great in most cases but what if the method is a RESTful WCF service method? Normally I would return a string or a list of string from a RESTful WCF Service or a RESTful Web API method so that the data can be sent across the web. If my method needs to return a complex type, how do I modify my method to make it work in a RESTful Service?

Garth J Lancaster

put a 'wrapper' around GetProductDetails maybe GetProductDetailsAsJSON and convert the returned list to a JSON encoded string

using Newtonsoft.Json;

and then ....

string productId = string.Empty; // ** Init this not Empty
List productionDetails = GetProductDetails(productId);
string ProductDetailsJSON = Newtonsoft.Json.JsonConvert.SerializeObject(productionDetails);
return ProductDetailsJSON;

[edit] obviously on the other end when the REST package/data is received you have to do something like :- // assuming you have the REST Data in a string called 'json', pretty dumb, but its only an example List productionDetails = Newtonsoft.Json.JsonConvert.DeserializeObject>(json); [/edit]

Lost User

Liagapi wrote:

At the moment I can only get a list which contains data from one table instead of all three tables.

You are already creating one. See the line "select new {employee.." That would create a new anonymous type, and that would be harder to return :)

Liagapi wrote:

I would like the list to contain employee, project, and product.

Create a new class that can hold the results, and return a list of that specific class.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)

Richard Deeming

Liagapi wrote:

IEnumerable<Employee> emps = db.Employees.ToList(); IEnumerable<Project> projs= db.Projects.ToList(); IEnumerable<Product> prods = db.Products.ToList();

Your code is loading the whole of those three tables into memory, before filtering for the specific product. That's going to be horribly inefficient.

Liagapi wrote:

string productId where prd.productID == Convert.ToInt32(productId)

Your code is going to throw an exception if you pass in any string that can't be converted to an integer. Instead, you should change your parameter to be an integer, so that your expectations are obvious to the caller.

Liagapi wrote:

public List<string> GetProductDetails

If you want to return something other than a single string for each record, you're going to need to change the return type.

Liagapi wrote:

join prj in projs on e.EmployeeID equals prj.ProjectID join prd in prods on e.EmployeeID equals prd.productID

Those join columns don't look right to me. Double-check how your models are related.

public sealed class ProductDetails
{
public string employee { get; set; }
public string project { get; set; }
public string product { get; set; }
}
...
public List<ProductDetails> GetProductDetails(int productId)
{
using (var db = new ProductContext())
{
var productDetails = from e in db.Employees
join prj in db.Projects on e.EmployeeID equals prj.ProjectID // Is this the correct join?
join prd in prods on e.EmployeeID equals prd.productID // Is this the correct join?
where prd.productID == productId
select new { e.EmployeeName, prj.ProjectName, prd.ProductName };

    return productDetails.AsEnumerable().Select(x => new ProductDetails
    {
        employee = x.EmployeeName,
        project = x.ProjectName,
        product = x.ProductName
    }).ToList();
}

}

Liagapi

Thank you all for replying. The joins are correct and I was able to get the results I was looking for. I've learned a lot from this exercise and your responses to my post helped tremendously.