Linq, EF Core - group by on one field and use other field to get list of data from other table

Question

I am trying to get a output from Linq in particular format. This question has been rephrased -

SQL View- SomeView

SQL table - User

    T_Id  fname lname
    2     mary   smith
    5     john    pope
    7     steve   blair

SomeView is QueryType, I used DbQuery to map it.

    public class SomeView
    {
       public int Id {get; private set;}
       public int T_Id { get; private set; }

       public User User { get; set; } //can't navigate here
    }

    public class User
    {
       public int T_Id { get; set; }
       public string fname {get; set;}
       public string lname{get; set;}

       public SomeView SomeView{ get; set; } //can't navigate here
    }

There are no foreign key constraint defined in database as SomeView is SQL view. You can't use Navigation with QueryType. So mapping between User and SomeView is not possible or I don't know how to do.

     public class SomeViewModel
     {
        public int Id { get;  set; }
        public List Users{get; set;}
     }

finally my linq in progress - 

    from t in SomeView
    group new {t} by t.Id into grp
    select new SomeViewModel{
       Id = grp.Key,
       Users = grp.Select(x => x.t.User).ToList()
       //need help here to get Users based on T_Id
      }

Final API data output should be in following format.

[{
  "Id" : "4",
  "users":[{
         "T_Id": 2,
         "fname": "mary",
         "lname": "smith"
      }]
},
{
  "Id" : "6",
  "users":[{
         "T_Id": 5,
         "fname": "john",
         "lname": "pope"
      },
      {
         "T_Id": 7,
         "fname": "steve",
         "lname": "blair"
      }
      ]
}]

Your tables imply many-to-one relationship from SomeTable to User. Hence List<User> Users in the view model makes no sense - each record in SomeTable can have 0 or 1 User. You'd better show the entity model - with proper navigation properties the LINQ query should be trivial - something like db.SomeTable.Select(t => new SomeViewModel { Id = t.Id, User = t.User }). — Ivan Stoev
But SomeTable is a view. Can't use HasMany on QueryTypeBuilder. — Shamal
modified question. SomeViewObj is QueryType used DbQuery to map it. I can't map it with HasMany on QueryTypeBuilder. — Shamal
Still my first comment applies. SomeViewObj is the many side of the relationship, so you need a single reference navigation property public User User { get; set; } mapped with HasOne and T_Id mapped with HasForeignKey. This is how we access data in EF Core queries. If you want regular LINQ, take a look at C# join clause — Ivan Stoev
You can't use Navigation properties for QueryType SQL View. I am adding details to my questions above — Shamal

Ali Abdoli Ali Abdoli · Accepted Answer · 2019-04-26T05:24:32

~~First, I think you can use any serializer to convert your objects to the format you want.~~

var serialized = JsonConvert.SerializeObject(data)

Second, back to your question, here is the code. However, you need to add " around your variables and get rid of string concatenation I added for readability. Also, this code is so specific to your problem and for more generic solution go for the first approach.

var mainData = string.Join(',', data.Select(x => $" {{ {nameof(x.Id)} :  {x.Id}, " +
                                                           $"{nameof(User)}s: " +
                                                           $"[ {string.Join(',', x.Users.Select(y => $"{{ {nameof(User.T_Id)} : {y.T_Id} }}"))}]" +
                                                           $"}}"));
var result = $"[{mainData}]" ;

As you changed the question, I updated my answer. So, you need to first join someView and user to get them together and then group by someView.id. Here is the code

        var someViewsUsersJoin = someViews.Join(users, l => l.t_id, r => r.t_id, (someView, user) => new {someView, user});

        var result = someViewsUsersJoin.GroupBy(x => x.someView.id).Select(x => new SomeViewModel()
        {
            Id = x.Key,
            Users = x.Select(y => y.user).ToList()
        });

Linq, EF Core - group by on one field and use other field to get list of data from other table

2 Answers