Return data from multiple tables using LINQ Method Syntax

Question

I have got problem with return data from my database. In my database are three tables: Customer, Activity and CustomerActivity. I want to return activities data for specific customer and with specific type activity. I wrote query but it doesn't work exactly as I wish. Here is my code and tables. Thank you for advices.

var activities = db.Join(db.CustomerActivities, a => a.ActivityID, ca =>ca.CustomerActivityID, (a, ca) => new { Activity = a, CustomerActivity = ca })
    .Where(a => a.CustomerActivity.customer.CustomerID == id && a.Activity.TypeActivityID == typeActivity)
    .Select(m => new ActivityMV()
    {
        ActivityID = m.Activity.ActivityID,
        Name = m.Activity.Name,
        DateOfActivity = m.Activity.DateOfActivity,
        Desc = m.Activity.Desc
    })
    .ToList();


public class CustomerActivity
{
    public int CustomerActivityID { get; set; }
    public int CustomerID { get; set; }
    public int ActivityID { get; set; }
    public virtual Customer customer { get; set; }
    public virtual Activity activity { get; set; }
}
public class Activity
{
    public int ActivityID { get; set; }
    public string Name { get; set; }
    public string Desc { get; set; }
    public string DateOfActivity { get; set; }
    public string TypeActivityID { get; set; }
    public virtual ICollection <CustomerActivity> customerActivities { get; set; }
}

"I wrote query but it doesn't work exactly as I wish." What is your actual result vs. your expected result? — Georg Patscheider
– Georg Patscheider, Commented Apr 6, 2018 at 9:09
There is no need of joins (db.Join(...) is not a valid construct anyway). Start the query from db.CustomerActivities and simply use CustomerActivity navigation properties (Customer and Activity) in Where and Select expressions. — Ivan Stoev
– Ivan Stoev, Commented Apr 6, 2018 at 9:11
To method I send two parameters: customer Id and type of activity. I want to get all activities for customer with specific type. I add that same data for two users and once in two different cases I get two diffrent results — KamLet
– KamLet, Commented Apr 6, 2018 at 9:21

M Bakardzhiev · Accepted Answer · 2018-04-06 09:27:21Z

1

Try

var activities = db.CustomerActivities
.Where(ca => ca.CustomerID == id && ca.Activity.TypeActivityID == typeActivity)
.Select(ca => new ActivityMV()
{
    ActivityID = ca.ActivityID,
    Name = ca.Activity.Name,
    DateOfActivity = ca.Activity.DateOfActivity,
    Desc = ca.Activity.Desc
})
.ToList();

edited Apr 6, 2018 at 9:27

answered Apr 6, 2018 at 9:12

M Bakardzhiev

6425 silver badges13 bronze badges

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

1 Comment

KamLet Over a year ago

Ohh, it works perfectly. One trifle: .Select(ca => new ActivityMV() Thanks!

Collectives™ on Stack Overflow

Return data from multiple tables using LINQ Method Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related