Web API Get Method Returns Empty in JSON

Question

I try to build a simple Web Api with entity framework. I get all rows from a table of my database and show all data as JSON object. But I have a problem about getting values of some columns, which has a relationship with another table. This problem causes to get empty JSON object. Below is my code for get method

public class ProjeOzet_Repository
    {
        private MGEOPROJETAKIPT_TEntities db = new MGEOPROJETAKIPT_TEntities();

        public IQueryable<ProjeOzet> GetProjeOzets()
        {

            var projeOzet = from p in db.PROJE_OZET
                            join pt in db.PO_PROJE_TURU on p.PROJE_TURU equals pt.ID
                            join dir in db.PO_DIREKTORLUK on p.P_YURUTEN_D equals dir.ID
                            join gb in db.PO_GRUP_B on p.P_YURUTEN_GB equals gb.ID
                            join mud in db.PO_MUDURLUK on p.P_YURUTEN_M equals mud.ID

                            join yon in db.Z_Personel_MGEO_BAK on (int)p.P_YONETICISI_SNO equals yon.SNO
                            join kat in db.PO_KATEGORI on p.P_KATEGORI equals kat.ID
                            join f in db.PO_FAZ on p.P_FAZ equals f.ID
                            select new ProjeOzet()
                            {
                                Id = p.PROJE_ID,

                                gizli = p.GIZLI,
                                raporlanacak = p.RAPORLANACAK,

                                projeNo = p.PROJE_NO_MGEO,
                                projeTanim = p.P_TANIM_MGEO,
                                projeGBId = p.P_YURUTEN_GB,
                                projeDirektorlukId = p.P_YURUTEN_D,
                                projeMudurlukId = p.P_YURUTEN_M,
                                projeTuru = pt.UZUN_AD,
                                projeTuruId = p.PROJE_TURU,

                                projeDirektorluk = dir.AD,
                                projeGB = gb.AD,
                                projeMudurluk = mud.AD,

                                yonetici = yon.AD + " " + yon.SOYAD,
                                sektorIci = p.SI_SA,

                                anaYukleniciSektor = p.ANA_YUKLENICI_S,
                                altYukleniciSektor = p.ALT_YUKLENICI_S, //Bu kısım arayüzde eşleştirilecek
                                projeKisaTanim = p.PROJE_KISA_TANIMI,
                                sozlesmeBaslangicTarihi = p.SOZLESME_BASLANGIC_TARIHI,
                                sozlesmeBitisGH = p.SOZLESME_BITIS_TARIHI_GH,
                                sozlesmeBitisGD = p.SOZLESME_BITIS_TARIHI_GD,
                                sozlesmeImzaTarihi = p.SOZLESME_IMZA_TARIHI,
                                aliciKurum = p.ALICI_KURUM,
                                ihtiyacSahibiKurum = p.IHTIYAC_SAHIBI_KURUM,
                                anaYukleniciKurum = p.ANA_YUKLENICI_KURUM,
                                kategori = kat.Aciklama,
                                faz = f.Aciklama,
                                ur_sp = p.UR_SP,
                                ekleyen = p.Ekleyen

                            };

            return projeOzet;
        }


    }

My controller for the object is;

public class ProjeOzetController : ApiController
    {
       private ProjeOzet_Repository projeOzetRepository;

        public ProjeOzetController()
        {
            this.projeOzetRepository = new ProjeOzet_Repository();
        }
        public IQueryable<ProjeOzet> Get()
        {
            return projeOzetRepository.GetProjeOzets();

        }
    }

My object model is;

public class ProjeOzet
    {
        public int Id { get; set; }

        public bool gizli { get; set; }
        public bool raporlanacak { get; set; }

        public string projeNo { get; set; }
        public string projeTanim { get; set; }
        public string projeTuru { get; set; }

        public int? projeTuruId { get; set; }
        public int? projeGBId { get; set; }
        public int? projeDirektorlukId { get; set; }
        public int? projeMudurlukId { get; set; }

        public string projeGB { get; set; }
        public string projeDirektorluk { get; set; }
        public string projeMudurluk { get; set; }

        public string yonetici { get; set; }
        public int? sektorIci { get; set; }
        public int? anaYukleniciSektor { get; set; }
        public string altYukleniciSektor { get; set; }
        public string projeKisaTanim { get; set; }
        public DateTime? sozlesmeImzaTarihi { get; set; }
        public DateTime? sozlesmeBaslangicTarihi { get; set; }
        public DateTime? sozlesmeBitisGH { get; set; }
        public DateTime? sozlesmeBitisGD { get; set; }
        public string aliciKurum { get; set; }
        public string ihtiyacSahibiKurum { get; set; }
        public string anaYukleniciKurum { get; set; }
        public string kategori { get; set; }
        public string faz { get; set; }
        public int? ur_sp { get; set; }
        public int? ekleyen { get; set; }

    }

For example, kategori variable has some problems. I have another table which is bound to this variable, and has two columns; ID (int) and Explanation (nvarchar). I save the ID value for kategori to my main table (which is under P_KATEGORI column of PROJE_OZET and it is nullable). I want to show the Explanation which is bounded that ID. For a row in my main table (PROJE_OZET) if P_KATEGORI has a value, there is no problem. But, if this column is null for this row, I get an empty JSON object when I call GET method, although other columns have values.

How can I solve this problem?

Answer 1

Most likely, you need to do a left join instead of an inner join. Here is an example of how to do that:

   var query = from person in people
                    join pet in pets on person equals pet.Owner into gj
                    from subpet in gj.DefaultIfEmpty();