C# Discussion Boards - CodeProject

Re: Return List From Joined Tables Using LINQ

Eddy Vluggen11-Feb-17 2:11

Eddy Vluggen

11-Feb-17 2:11

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[^]

Re: Return List From Joined Tables Using LINQ

Richard Deeming12-Feb-17 1:38

Richard Deeming

12-Feb-17 1:38

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

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

Re: Return List From Joined Tables Using LINQ

Liagapi12-Feb-17 6:11

Liagapi

12-Feb-17 6:11

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.

Virtual Trackpad > WIndows...

qzzf1w10-Feb-17 9:12

qzzf1w

10-Feb-17 9:12

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