mrapi
mrapi

Reputation: 6015

Convert multiple SQL JOINs to Entity Framework

I'm trying to convert this SQL query:

select t.id,t.name as table_name,r.name as room_name,iif(c.id is null ,0,1) as opened,
 from gs_pos_t t
inner join gs_pos_r r on t.id_r=r.id
left join gs_pos_c c on t.id=c.id_t
left join gs_pos_pz p on c.id=p.id_c
 where c.open_date is not null
 group by 1,2,3,4

I need to select all tables from all rooms and the status if there are opened sales on them,in this case the sum of current opened sale

I've tried with no luck something like this:

        db.GS_POS_t.Join(
                      db.GS_POS_r,
                      t => t.ID_r,
                      r => r.ID,
                   (tables, rooms) => new {tables,rooms})
               .GroupJoin(
                     db.GS_POS_C
                       .GroupJoin(
                           db.GS_POS_C_PZ,
                           c => c.ID,
                           p => p.ID_C,
                           (sales, p) => new { sales, s_detail= p.DefaultIfEmpty() }
                           )
                           ,
                     mg => mg.tables.ID,
                     cg => cg.sales.ID_t,
                     (tablesrooms, saleswithdetail) => new { tablesrooms, saleswithdetail }
                ).Where(r => r.saleswithdetail.) <=Here !!!!
            );

this is the model:

GS_POS_T  (tables)
        public long ID { get; set; }
        public short ID_R { get; set; }
        public string NAME { get; set; }
        public virtual ICollection<GS_POS_C> GS_POS_C { get; set; }
        public virtual GS_POS_R GS_POS_R { get; set; }
       ...

GS_POS_R (rooms)

        public short ID { get; set; }
        public string NAME { get; set; }
        public virtual ICollection<GS_POS_T> GS_POS_T { get; set; }
        ...

GS_POS_C (sales)
        public long ID { get; set; }
        public long ID_T { get; set; }
        public Nullable<System.DateTime> OPEN_DATE { get; set; }
        ......    
        public virtual GS_POS_M GS_POS_M { get; set; }
        public virtual ICollection<GS_POS_C_PZ> GS_POS_C_PZ { get; set; }


GS_POS_C_PZ  (sale details)
        public long ID { get; set; }
        public long ID_C { get; set; }
        public Nullable<decimal> CANT { get; set; }
        public Nullable<decimal> PRICE { get; set; }
        public virtual GS_POS_C GS_POS_C { get; set; }
       ...

PARTIAL SOLUTION I got next code that almost works,but 2 problems:

1.I can't include in final result ID field from db.GS_POS_C

ExceptionMessage":"The cast to value type 'System.Int64' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."

2.I want to convert it to method syntax

from t in db.GS_POS_T
        join r in db.GS_POS_R on t.id_r equals r.id //it is inner join statement
        join c in db.GS_POS_C on t.id equals c.id_t  into ct
              from sub1 in ct.DefaultIfEmpty()  //it is left join statement
        join p in db.GS_POS_PZ on sub1.id equals p.id_c into psub1
              from sub2 in psub1.DefaultIfEmpty() //it is left join statement
              where t.activ == 1
        select new { t.id, t.name, rname = r.name,  sub2.cant, sub2.price } into x
        group x by new { id = x.id, name = x.name, rname = x.rname } into g
        select new
        {
            tid = g.Key.id,
            tname = g.Key.name,
            rname = g.Key.rname,
            sumpcantpprice = g.Sum(abc => abc.price * abc.cant)
        };

thanks for any help!

Upvotes: 0

Views: 292

Answers (1)

alaa_sayegh
alaa_sayegh

Reputation: 2211

for the left outer join in linq you can use this syntax:

DefaultIfEmpty(new Model())

So, this may give you an idea, i wrote it quickly on the fly, so it may not wrok from the first try. I don't know your models, so replace the models in the linq statement and give it a try:

    var result = from t in gs_pos_t

                        join r in gs_pos_r on t.id_r equals r.id

                        join c in gs_pos_c on t.id equals c.id_t into firstList from lt1 in firstList.DefaultIfEmpty(new Model1())

                        join p in gs_pos_pz onc.id equals p.id_c into secondList from lt2 in secondList.DefaultIfEmpty(new Model2())

                        where c.open_date != null

                        select new
                        {
                            t.id,
                            t.name,
                            r.name,
                            lt1.id == null ? 0 : 1
                        };

Hope it helps

Upvotes: 1

Related Questions