CODEWITHSUNDEEP
c#entity-framework
Yuropoor
Yuropoor

Reputation: 353

Problem with selecting join result into result

I'm new to the EF. To keep it simpler, let's say until recently, I had a table like so

CREATE TABLE customer
(
    CustomerNumber int,
    CustomerName nvarchar(100)
)

I had a class to handle this

[Table("customer")]
public class Customer
{
    [Key]
    public int CustomerNumber { get; set; }
    public string CustomerName { get; set; }
}

With context:

public class MyContext : DbContext
    {
        public MyContext()
                : base("name=MyContext")
        {
        }

        public DbSet<Customer> Customers { get; set; }

    }

With controller:

public class CustomersController : ODataController
    {
        MyContext db = new MyContext();

        private bool CustomerExists(string key)
        {
            return db.Customers.Any(p => p.CustomerNumber == key);
        }

        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }

        [EnableQuery(PageSize =20)]
        public IQueryable<Customer> Get()
        {
            db.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
            return db.Customers;
        }

        [EnableQuery]
        public SingleResult<Customer> Get([FromODataUri] string key)
        {
            IQueryable<Customer> result = db.Customers.Where(p => p.CustomerNumber == key);
            return SingleResult.Create(result);
        }

    }

Now recently I have a new table with new data to display

CREATE TABLE customerDetails
(
    CustomerNumber int,
    Role nvarchar(20),
    ContactName nvarchar(30)
)

For each customer number, there are 2 entries in customerDetails table, differing on role, let's say A and B.

What I'm tryin to do is to get the result of the following query into my modified entity

Query:

SELECT c.CustomerNumber, c.CustomerName, cc1.ContactName as A_Name, cc2.ContactName as B_Name
FROM customer as c
JOIN customerDetails as cc1 ON c.CustomerNumber = cc1.CustomerNumber AND cc1.Role = 'A'
JOIN customerDetails as cc2 ON c.CustomerNumber = cc2.CustomerNumber AND cc2.Role = 'B'

I modified my entity:

[Table("customer")]
public class Customer
{
    [Key]
    public int CustomerNumber { get; set; }
    public string CustomerName { get; set; }
    public string A_Name { get; set; }
    public string B_Name { get; set; }
}

Added: 

[Table("customerDetails")]
public class Customer
{
    [Key]
    public int CustomerNumber { get; set; }
    public string Role { get; set; }
    public string ContactName { get; set; }
}

And modified the controller:

[EnableQuery(PageSize =20)]
        public IQueryable<Customer> Get()
        {
            db.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
            return from c in db.Customers
                   join cc1 in db.CustomerContacts.Where(e => e.Role == "A") on c.CustomerNumber equals cc1.CustomerNumber
                   join cc2 in db.CustomerContacts.Where(e => e.Role == "B") on c.CustomerNumber equals cc2.CustomerNumber
                   select c;
        }

But it returns an empty set. How can I properly write that select statement here and populate my modified Customer entity?

Upvotes: 0

Views: 46

Answers (1)

Steve Py
Steve Py

Reputation: 34683

Your entities should reflect your overall table structure. Given a Customer entity with a CustomerDetails entity and a 1-many relationship between the two on CustomerNumber I would target the entities as:

public class Customer
{
   public int CustomerNumber {get; set;}
   public virtual ICollection<CustomerDetails> CustomerDetails {get; set;}
}

public class CustomerDetails
{
   public int CustomerDetailId {get; set;}
   public string Role {get; set;}
   public int CustomerNumber {get; set;}
}

... and map the customer and customer details. Note the CustomerDetails would have it's own PK column, not CustomerNumber since for a single customer you'd have potentially 2x customer details (role A and role B)

I'd advise against passing entities to your view, instead, create a view model for the view to consume and select the relevant details from your entities using .Select. This is best for performance, allows you to format/flatten the data to suit your view, and is much more secure than exposing your entire entity graph to a consumer.

Alternatively, if you want a specific set of related roles (optional) for a customer, then the customer table should have a RoleACustomerDetailsId and RoleBCustomerDetailsId as int? which can then reference these related entities.

Upvotes: 1

Related Questions