Question: I/P:

Employee DF

```
Name | Project 

| A  | p1,p2

| B   | p1

| C   | p2      

```

O/P:

Project DF

```
| proj| Count|

| p1  | 2            |

| p2   | 2          |

```

I need to count the number of employees working on each project. I tried below snippet but it's not giving me the correct answer:

```
df = spark.createDataFrame(sc.parallelize([['A', 'p1, p2'], ['B', 'p1'], ['C', 'p2']]), ["name", "project"])

df2 = df.withColumn('proj',explode(split(df['project'],",")))

df2.groupBy('proj').count().show()

```

My output

```
| proj| Count|

| p2  | 1  

| p2   | 1  

| p1   | 2   

```

I don't know why it is not performing groupBy on p2.

Answer: 
You have to create custom projection

```cs
var query = 
  from c in db.VW_Customer
  select new OrderReport
  {
     id = c.id,
     name = c.name
     orders = db.VW_Order.Where(o => o.Customer_id == c.id)
        .Select(o => new Order
        {
           item = o.Item,
           price = o.Price
        })
        .ToArray()
  };

var result = query.ToArray();

```

**Update**

If there is no chance to make it work, since EF Core for eager loading needs entities with keys. You can use client-side combining.

```cs
var customers = db.VW_Customer.ToList();
var orders = db.VW_Order.ToList();

var query =
  from c in customers
  join o in orders on c.id equals o.Customer_id into g 
  select new OrderReport
  {
     id = c.id,
     name = c.name
     orders = g.Select(o => new Order
              {
                 item = o.Item,
                 price = o.Price
              })
             .ToArray()
  };

var result = query.ToArray();

```
I found out in order to use Eager Loading (first solution by @Svyatoslav) I had to make few adjustments.

*Note: If you are doing DB First and you regenerate the model all changes will be lost*

I had to explicitly set the key property in the Model with the ["Key"] attribute

```
Public Class VW_Customer
{   [Key]
    public int id { get; set; }
    public string name { get; set; }
}

Public Class VW_Order
{
    public int Customer_id { get; set; }
    [Key]
    public int Oder_id{ get; set; }
    public string Item { get; set; }
    public decimal Price { get; set; }
}

```

I then had to change the DBContet Class to specify that the VW\_Customer and VW-Order had Alternative keys. To do this i remove this line for both entities

```
 entity.HasNoKey(); 

```

and replace it with

```
      modelBuilder.Entity<VW_Customer>(entity =>
            {
               entity.HasAlternateKey(x => x.Id);
               ......

      modelBuilder.Entity<VW_Order>(entity =>
            {
               entity.HasAlternateKey(x => x.Order_Id);
               ......

```

After doing that i was able to generate the desired output based on Svyatoslav first solution.
The problem with this approach is that if the model is regenerated from the DB then all the changes made above will be lost.

If there is a clever way of preventing these changes from being overwritten (after regenerating the model from the DB) please suggest it as it could be helpful.