Linq Sub Query (SELECT in SELECT)

Question

Linq Sub Query (SELECT in SELECT)

entity entity-framework linq subquery

Question

I have got a native join sub query problem in MySQL Entity. SQL code is;

DB structure is;

tbl_urunler_kod
id, kod
- 1, pkod1
- 2, pkod2
- 3, pkod3

tbl_urunler
id, kod_id
- 1, 1
- 2, 2
- 3, 3
- 4, null
- 5, null
- 6, null
- 7, null
- 8, null
- 9, null

SELECT (SELECT k.kod FROM tbl_urunler_kod k WHERE k.id=t.kod_id) AS kod FROM tbl_urunler t

its giving 9 records.

I try this,

 var SQL = (from p in SME.tbl_urunler
                   from k in SME.tbl_urunler_kod
                              where k.id == p.kod_id
                   select new
                   {
                       kod = k.kod
                   });

its returning only 3 records. Because tbl_urunler_kod have got only not null 3 records. Other 6 records is null. I wanna see all records. How can i do this.

And i try this;

var SQL2 = (from p in SME.tbl_urunler
                       select new
                       {
                           kod = (from k in SME.tbl_urunler_kod where k.id == p.kod_id select new { k.kod })
                       });

Its giving this error;

'System.Data.Common.Internal.Materialization.CompensatingCollection`1[f__AnonymousType0`1[System.String]]' tÃ¼rÃ¼ndeki nesne 'System.String' tÃ¼rÃ¼ne atÄ±lamadÄ±.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidCastException: 'System.Data.Common.Internal.Materialization.CompensatingCollection`1[f__AnonymousType0`1[System.String]]' tÃ¼rÃ¼ndeki nesne 'System.String' tÃ¼rÃ¼ne atÄ±lamadÄ±.

Thanks for your help.

Ahmet YETÄ°K

Popular Answer

this is LingtoSql lambda expression

    var query = SME.tbl_urunler.Where
                (x=>x.tbl_urunler_kod.id == x.kod_id  || 
                x.kod_id == null).Select(x=>new {x.id})

Esi

View more on Stack Overflow

Ahmet YETÄ°K · Accepted Answer · 12/16/2011 9:48:31 AM

I was created a little simulatin for this. Please look the results;

public class tbl_urunler_kod
{
    public int ID { get; set; }
    public string Kod { get; set; }
    public tbl_urunler_kod() { }
    public tbl_urunler_kod(int _ID, string _Kod)
    {
        _ID = ID;
        _Kod = Kod;
    }
}

public class tbl_urunler
{
    public int ID { get; set; }
    public string Adi { get; set; }
    public int KodID { get; set; }
    public tbl_urunler() { }
    public tbl_urunler(int _ID, string _Adi, int _KodID)
    {
        _ID = ID;
        _Adi = Adi;
        _KodID = KodID;
    }
}

public List<tbl_urunler_kod> L_tbl_urunler_kod = new List<tbl_urunler_kod>();
public List<tbl_urunler> L_tbl_urunler = new List<tbl_urunler>();

protected void Page_Load(object sender, EventArgs e)
{
    Fill();
    Result1();
    Result2();
    Result3();
    Result4();
    Result5(); //Its working
    Result6(); //Its working
    Result7(); //Its working
}

public void Fill()
{
    #region tbl_urunler_kod filling
    L_tbl_urunler_kod.Add(new tbl_urunler_kod { ID = 1, Kod = "Kod_1" });
    L_tbl_urunler_kod.Add(new tbl_urunler_kod() { ID = 2, Kod = "Kod_2" });
    L_tbl_urunler_kod.Add(new tbl_urunler_kod() { ID = 3, Kod = "Kod_3" });
    #endregion

    #region tbl_urunler filling
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_1", ID = 1, KodID = 1 });
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_2", ID = 2, KodID = 2 });
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_3", ID = 3, KodID = 3 });
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_4", ID = 4 });
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_5", ID = 5 });
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_6", ID = 6 });
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_7", ID = 7 });
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_8", ID = 8 });
    L_tbl_urunler.Add(new tbl_urunler() { Adi = "Urun_9", ID = 9 });
    #endregion
}

public void Result1()
{
    Response.Write("<h1>Result 1</h1>");

    var SQL = (from p in L_tbl_urunler
               from k in L_tbl_urunler_kod
               where k.ID == p.KodID
               select new
               {
                   p.Adi,
                   kod = k.Kod
               }); //result count = 3 record;

    foreach (var i in SQL)
    {
        Response.Write(i.Adi + " - " + i.kod + "<br/>");
    }

    Response.Write("<br/><hr/>");
}

public void Result2()
{
    Response.Write("<h1>Result 2</h1>");

    var SQL = (from p in L_tbl_urunler
               from k in L_tbl_urunler_kod
               where k.ID == p.KodID || p.KodID == 0
               select new
               {
                   p.Adi,
                   kod = k.Kod
               }); //result count = 21 record;

    foreach (var i in SQL)
    {
        Response.Write(i.Adi + " - " + i.kod + "<br/>");
    }

    Response.Write("<br/><hr/>");
}

public void Result3()
{
    Response.Write("<h1>Result 3</h1>");

    var SQL = (from p in L_tbl_urunler
               let LKod = L_tbl_urunler_kod.Where(k => k.ID == p.KodID).Select(x => x.Kod)
               select new
               {
                   p.Adi,
                   kod = LKod
               }); //result count = 9 record;

    foreach (var i in SQL)
    {
        Response.Write(i.Adi + " - " + i.kod + "<br/>");
        //System.Linq.Enumerable+WhereSelectListIterator`2[test+tbl_urunler_kod,System.String]
    }

    Response.Write("<br/><hr/>");
}

public void Result4()
{
    Response.Write("<h1>Result 4</h1>");

    var SQL = (from p in L_tbl_urunler
               select new
               {
                   p.Adi,
                   kod = (from k in L_tbl_urunler_kod where k.ID == p.KodID select new { k.Kod })
               }); //result count = 9 record;

    foreach (var i in SQL)
    {
        Response.Write(i.Adi + " - " + i.kod + "<br/>");
        //System.Linq.Enumerable+WhereSelectListIterator`2[test+tbl_urunler_kod,<>f__AnonymousType3`1[System.String]]
    }

    Response.Write("<br/><hr/>");
}

public void Result5()
{
    Response.Write("<h1>Result 5</h1>");

    var SQL = (from p in L_tbl_urunler
               select new
               {
                   p.Adi,
                   kod = L_tbl_urunler_kod.Where(k => k.ID == p.KodID).Select(x => x.Kod).FirstOrDefault()
               }); //result count = 9 record;

    foreach (var i in SQL)
    {
        Response.Write(i.Adi + " - " + i.kod + "<br/>");
    }

    Response.Write("<br/><hr/>");
}

public void Result6()
{
    Response.Write("<h1>Result 6</h1>");

    var SQL = (from p in L_tbl_urunler
               select new
               {
                   p.Adi,
                   kod = (from k in L_tbl_urunler_kod where k.ID == p.KodID select new { k.Kod }).FirstOrDefault()
               }); //result count = 9 record;

    foreach (var i in SQL)
    {
        Response.Write(i.Adi + " - " + i.kod + "<br/>");
    }

    Response.Write("<br/><hr/>");
}

public void Result7()
{
    Response.Write("<h1>Result 7</h1>");

    var SQL = (from p in L_tbl_urunler
               let LKod = L_tbl_urunler_kod.Where(k => k.ID == p.KodID).Select(x => x.Kod).FirstOrDefault()
               select new
               {
                   p.Adi,
                   kod = LKod
               }); //result count = 9 record;

    foreach (var i in SQL)
    {
        Response.Write(i.Adi + " - " + i.kod + "<br/>");
    }

    Response.Write("<br/><hr/>");
}

Result 1

Urun_1 - Kod_1
Urun_2 - Kod_2
Urun_3 - Kod_3

Result 2

Urun_1 - Kod_1
Urun_2 - Kod_2
Urun_3 - Kod_3
Urun_4 - Kod_1
Urun_4 - Kod_2
Urun_4 - Kod_3
Urun_5 - Kod_1
Urun_5 - Kod_2
Urun_5 - Kod_3
Urun_6 - Kod_1
Urun_6 - Kod_2
Urun_6 - Kod_3
Urun_7 - Kod_1
Urun_7 - Kod_2
Urun_7 - Kod_3
Urun_8 - Kod_1
Urun_8 - Kod_2
Urun_8 - Kod_3
Urun_9 - Kod_1
Urun_9 - Kod_2
Urun_9 - Kod_3

Result 3

Urun_1 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]
Urun_2 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]
Urun_3 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]
Urun_4 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]
Urun_5 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]
Urun_6 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]
Urun_7 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]
Urun_8 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]
Urun_9 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,System.String]

Result 4

Urun_1 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]
Urun_2 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]
Urun_3 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]
Urun_4 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]
Urun_5 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]
Urun_6 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]
Urun_7 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]
Urun_8 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]
Urun_9 - System.Linq.Enumerable+WhereSelectListIterator2[test+tbl_urunler_kod,<>f__AnonymousType31[System.String]]

Result 5

Urun_1 - Kod_1  
Urun_2 - Kod_2  
Urun_3 - Kod_3  
Urun_4 -  
Urun_5 -  
Urun_6 -  
Urun_7 -  
Urun_8 -  
Urun_9 -

Result 6

Urun_1 - { Kod = Kod_1 }  
Urun_2 - { Kod = Kod_2 }  
Urun_3 - { Kod = Kod_3 }  
Urun_4 -  
Urun_5 -  
Urun_6 -  
Urun_7 -  
Urun_8 -  
Urun_9 -

Result 7

Urun_1 - Kod_1  
Urun_2 - Kod_2  
Urun_3 - Kod_3  
Urun_4 -  
Urun_5 -  
Urun_6 -  
Urun_7 -  
Urun_8 -  
Urun_9 -

Thanks.

Linq Sub Query (SELECT in SELECT)

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related