Sub Query in Linq (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.

1
1
11/16/2012 1:39:46 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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.

1
12/16/2011 9:48:31 AM

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

Related Questions

Select * from Entities where Id = (select max(Id) from Entities) in Entity Framework

Can you try something like this?...using(var container = new TheModelContainer()) { string maxEntityName = container.Entities.Max(e => e.Name); Entity maxEntity = container.Entities .Where(e => e.Name == maxEntityName) .FirstOrDefault(); } ...That would select the maximum value for ...Na...
entity-framework
asked by marc_s

Which LINQ query to select rows from 1 table that are not in another table

Getting the distinct records from your original query should get you the desired result. Note the Distinct() before Take()....var query = (from p in _context.Set<Products>() join sc in _context.Set<ShoppingCarts>() on p.Id equals sc.ProductId into subset from sc in subset.DefaultIfEmpty() where !p.Sol...
c# entity-framework linq linq-to-entities
asked by Dave Turvey

"SELECT VALUE" - value keyword in LINQ/Entity Framework query

That is ...Entity SQL... syntax. ...Value... keyword allows only one value to be specified, and does not add a row wrapper....Read ...article about SELECT statement in ESQL...Entity SQL supports two variants of the SELECT clause. The first variant, row select, is identified by the SELECT keyword, and can be used to specify one or more values th...
entity-framework linq linq-to-entities
asked by Sergey Berezovskiy

LINQ Query Using Navigation Properties Generates Multiple SELECT Statements

As said in the comment you need the ...Include... method:...SalesOrderHeaders.Include(s => s.SalesOrderDetails .Select(d => d.SpecialOfferProduct)) .Where(s => s.SalesOrderID == 43659) .Single().SalesOrderDetails ...this will join the required data (in SQL) and populate the navigatio...
c# entity-framework linq linq-to-entities sql
asked by Gert Arnold

Entity Framework - select group by, select max date

Try following... return _ctx.Notes.Select(r => new NoteBrief { Id = r.Id, Title = r.Title, Created = r.Created, ParentNoteId = r.ParentNoteId, }).OrderBy(x=>x.Created).GroupBy(x=>new {Id=x.Id, Title=x.Title}).Select(x=>x.First()).AsQueryable(); ...}
entity-framework
asked by Tilak

.Include() .Where() .Select() in Linq to Entities Query

.Include(...)... 'suggests' to the query provider to eager load the navigation property. In this case, it won't do so b/c your results are not ...Task... entities, they're ...Entity... entities. ...In any case, you don't filter a collection navigation property in an include statement (which is what's causing your error). ...You want the followin...
c# entity-framework linq-to-entities sql
asked by Moho

Linq To Entities, Select multiple sets of data with one query

Well, I'm sure there are many ways to do this, but if you want to avoid introducing a third ...DbSet... into the mix......var query = (from s in context.Students.Take(1) select new { subSetA = context.Students.ToList(), subSetB = context.Buildings.ToList(), }) ...Then, you can...
c# entity-framework entity-framework-6 linq linq-to-entities
asked by jjj

select from a list with field name and field value with linq (similar to select command)

After searching I found the Answer ,Answer is:... public class SearchField { public string Name { get; set; } public string @Value { get; set; } //public string Operator { get; set; } public SearchField(string Name, string @Value) { this.Name = Name; this.@Value = @Value; ...
c# entity-framework-6 linq sql-server
asked by Hootan.Ghiasi

How to Select subset of columns from SingleOrDefault LINQ query in EF

var personid = ctx.persons .Where(p => p.login == currentLogin) .Select(p => new {Prop = p.Column, personid = p.id}) .SingleOrDefault()?.personid; ...Would probably work.
c# entity-framework entity-framework-6 linq
asked by Joakim M. H.

Why does this linq query fail when I change the select clause to select new Model

Because you are using ...LINQ To SQL... here which means, that query is translated to ...SQL... that has no idea about your custom types.... var rooms = (from m in db.Rooms where m.FK_HotelID == id select m).ToList(); var roomsVm = from m in rooms select new Room ...
ado.net-entity-data-model asp.net-mvc-5 c# entity-framework-6 linq
asked by madoxdev

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow