CODEWITHSUNDEEP
c#sqllinqjoindatagridview
Ali Zangeneh
Ali Zangeneh

Reputation: 77

dont Show Parent DataTable If they have not child in datagridview

i will try build some cascade datagridview

Layer

layerID,LayerName (LayerID = PK )

Gor

LayerID , GorID , GorName ( GorID = PK and LayerID = FK )

Gru

GorID , GruID , GruName ( GruID = Pk and GorID = FK )

i start with this code

using (UnitOfWork db = new UnitOfWork())
            {

                DGVCharts.AutoGenerateColumns = false;


                var result = from layer in db.LayerRepository.Get()
                             join gor in db.GorRepository.Get() on layer.LayerID equals gor.LayerID

                             select new
                             {
                                 layer.LayerID,
                                 layer.LayerName,
                                 gor.GorID,
                                 gor.GorNumber,
                             };
                DGVCharts.DataSource = result.ToList();

            }

after i use top code i see this and its ok enter image description here

now i will try load more colum i grow my code to this

using (UnitOfWork db = new UnitOfWork())
            {

                DGVCharts.AutoGenerateColumns = false;


                var result = from layer in db.LayerRepository.Get()
                             join gor in db.GorRepository.Get() on layer.LayerID equals gor.LayerID
                             join gru in db.GruRepository.Get() on gor.GorID equals gru.GorID
                             select new
                             {
                                 layer.LayerID,
                                 layer.LayerName,
                                 gor.GorID,
                                 gor.GorNumber,
                                 gru.GruID,
                                 gru.GruNumber
                             };
                DGVCharts.DataSource = result.ToList();

            }

after update my code i see this

enter image description here

where is manager in (Gor Colum) ?? manager no child and i cant see manager in my datagridview !

how i can see manager too ?

Upvotes: 1

Views: 105

Answers (1)

Mohammed Sajid
Mohammed Sajid

Reputation: 4903

By using Left join, you can get manager if not has any relation in Gru Table.
I'm testing the following code for LinqToObject : `

1 - Creating test classes:

public class Layer
{
    public int LayerId { get; set; }
    public string LayerName { get; set; }
}
public class Gor
{
    public int GorId { get; set; }
    public int LayerId { get; set; }
    public string GorName { get; set; }
}
public class Gru
{
    public int GruId { get; set; }
    public int GorId { get; set; }
    public string GruName { get; set; }
}
public class Das
{
    public int DasId { get; set; }
    public int GruId { get; set; }
    public string DasName { get; set; }
}

2 - Initializing the collections:

List<Layer> layers = new List<Layer>
{
    new Layer{LayerId=1, LayerName="Main"}
};

List<Gor> gors = new List<Gor>
{
    new Gor{GorId=1, GorName="team01", LayerId=1},
    new Gor{GorId=2, GorName="team02", LayerId=1},
    new Gor{GorId=3, GorName="manager", LayerId=1},
};

List<Gru> grus = new List<Gru>
{
    new Gru{GruId=1, GruName="leader", GorId=1},
    new Gru{GruId=2, GruName="co-leader", GorId=1},
    new Gru{GruId=3, GruName="01", GorId=1},
    new Gru{GruId=4, GruName="leader", GorId=2},
    new Gru{GruId=5, GruName="co-leader", GorId=2},
    new Gru{GruId=6, GruName="01", GorId=2},
};
List<Das> dasList = new List<Das>
{
    new Das{DasId=1, DasName="das1", GruId=1},
    new Das{DasId=2, DasName="das2", GruId=5},
};

3 - Linq request:

var result = (from layer in layers
              join gor in gors on layer.LayerId equals gor.LayerId
              join gru in grus on gor.GorId equals gru.GorId into leftedGrus
              from leftedGru in leftedGrus.DefaultIfEmpty()
              join das in dasList on leftedGru?.GruId equals das.GruId into leftDasList
              from leftDas in leftDasList.DefaultIfEmpty()
              select new
              {
                  layer.LayerId,
                  layer.LayerName,
                  gor.GorId,
                  gor.GorName,
                  leftedGru?.GruId,
                  leftedGru?.GruName,
                  leftDas?.DasId,
                  leftDas?.DasName
              }).ToList();

4 - Demo

foreach(var item in result)
{
    Console.WriteLine($"Layer: {item.LayerId}::{item.LayerName}," +
        $"Gor: {item.GorId}::{item.GorName},Gru: {item.GruId}::{item.GruName}" +
        $",Das: {item.DasId}::{item.DasName}");
}

5 - Result

Layer: 1::Main,Gor: 1::team01, Gru: 1::leader   ,Das: 1::das1
Layer: 1::Main,Gor: 1::team01, Gru: 2::co-leader,Das:  ::
Layer: 1::Main,Gor: 1::team01, Gru: 3::01       ,Das:  ::
Layer: 1::Main,Gor: 2::team02, Gru: 4::leader   ,Das:  ::
Layer: 1::Main,Gor: 2::team02, Gru: 5::co-leader,Das: 2::das2
Layer: 1::Main,Gor: 2::team02, Gru: 6::01       ,Das:  ::
Layer: 1::Main,Gor: 3::manager,Gru:  ::         ,Das:  ::

Note that, GruId and GruName are null because no manager in Gru Table.

I hope this help you fix the issue.

Upvotes: 1

Related Questions