Re: Return List From Joined Tables Using LINQ - C# Discussion Boards

The problem is not with your code since your code doesn't do anything at all with the conversion, other than create an instance of a class that does all the work for you.

You have no choice but to get with the support people of the company that made that library and take up the issue with them.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

We can't help you.

Member 12757695 wrote:
Third part software is sautinSoft.Pdffocus.

Ask them they should know.

Patrice

“Everything should be made as simple as possible, but no simpler.” Albert Einstein

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

C#

public List<string> GetProductDetails(string productId)
{
  var db = new ProductContext();
  IEnumerable<Employee> emps = db.Employees.ToList();
  IEnumerable<Project> projs= db.Projects.ToList();
  IEnumerable<Product> 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.

modified 11-Feb-17 1:22am.

my first worries are :-

C#

public List<string> GetProductDetails(string productId)

and

C#

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

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?

modified 11-Feb-17 7:04am.

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

C#

using Newtonsoft.Json;

and then ....

C#

string productId = string.Empty;  // ** Init this not Empty
List<prodDetails> 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<proddetails> productionDetails = Newtonsoft.Json.JsonConvert.DeserializeObject<list<proddetails>>(json);

[/edit]

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 Smile | :)

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

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.

C#

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();
    }
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

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.

modified 12-Feb-17 12:53pm.

I am trying to implement a virtual trackpad (WPF or WInforms). Problem is that every time I touchdown or touchmove on the screen the mouse cursor goes under my finger. I can programatically move the cursor elsewhere but it becomes a tug-of-war with WIndows.

Any thoughts on resolving this?

Thanks

-----
Jack

Prevent a WPF application to interpret touch events as mouse events?