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.
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.
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})