I am new to linq & entity framework and would like your help

I have 3 tables in sql from which I want the data to be combined

1. query table -- it has userId to join user table

2. user table --- only want user data eg. username from this table for the query -- this is always single

3. docs table -- It has queryId to join queryTable also it is not necessary that each query should have a doc that's why here the join is left outer because I still want the query where there are no docs and also it is possible that a query can have mutiple docs for it

my linq query is as such

var result = (from q in queryTable 
              join u in userTable on q.userId equals u.Id 
              join doc in docTable on q.Id equals doc.queryId into qdoc
              from doc in qdoc.DefaultIfEmpty()
              select new {
              Id = q.Id,
              Text = q.Text,
              User = u.UserName,
              Doc = doc == null ? null : new {DocId = doc.Id, DocName = doc.DocumentName}
              })

now the above query works if there is single doc for a query but it case of mutiple doc for a query it gives me 2 entries for that query

Now suppose I have 3 query ids 1,2,3 for id=1 there is single doc, for id=2 there are two docs and for id=3 there is no doc

when I run above query for id=1 the output is as such

{
 "id": 1,
 "text": "Some text of query",
 "user": "Username",
 "doc": {
          "docId": 2,
          "docName": "pictue.png",
        }
}

for id=3 no docs

{
     "id": 3,
     "text": "Some text of query",
     "user": "Username",
     "doc": null
    }

problem is when id=2 since there are 2 docs it gives me 2 entries which I dont want

-- output which I get --

{
     "id": 2,
     "text": "Some text of query",
     "user": "Username",
     "doc": {
              "docId": 3,
              "docName": "pictue.png",
            },

    },
 {
     "id": 2,
     "text": "Some text of query",
     "user": "Username",
     "doc": {
              "docId": 4,
              "docName": "pictue.png",
            }
    }

Expected output ---

{
     "id": 2,
     "text": "Some text of query",
     "user": "Username",
     "doc": [{
              "docId": 3,
              "docName": "pictue.png",
            },
             {
              "docId": 4,
              "docName": "pictue.png",
            }]

    }

CodePudding user response：

It is normal behavior for JOIN, it multiplies result sets. But EF have implemented Eager Loading which may group entities for final result set.

var result = 
    from q in queryTable 
    join u in userTable on q.userId equals u.Id 
    select new 
    {
        Id = q.Id,
        Text = q.Text,
        User = u.UserName,
        Doc = docTable.Where(doc => doc.queryId == q.Id)
            .Select(doc => new { DocId = doc.Id, DocName = doc.DocumentName })
            .ToArray()
    };

Also consider using navigation properties extensively. In this case explicit join to user table will be not needed.