Esempio n. 1
0
        static void Main(string[] args)
        {
            using (var db = new ExDbModel())
            {
                DataConnection.TurnTraceSwitchOn();
                DataConnection.WriteTraceLine = (s, s1) => { Console.WriteLine(s); };

                db.CreateTable <billing_Devtype>();
                db.CreateTable <billing_Device>();
                db.CreateTable <billing_DevReadingType>();
                db.CreateTable <billing_TempReading>();

                #region Source is table (correct build)
                db.TempReadings
                .Set(p => p.DevReadingTypeId,
                     u => db.DevReadingTypes.Where(w => w.Name == u.ReadingTypeName && w.DevTypeId == u.Devtypeid).Select(s => s.Id).FirstOrDefault()
                     )
                .Set(p => p.Responsibility,
                     u => db.DevReadingTypes.Where(w => w.Name == u.ReadingTypeName && w.DevTypeId == u.Devtypeid).Select(s => s.Responsibility).FirstOrDefault()
                     )
                .Update();

                #region Sql correct resault

                /*
                 *  UPDATE
                 *      billing."TempReading"
                 *  SET
                 *      "DevReadingTypeId" = (
                 *              SELECT
                 *                      w_1."Id"
                 *              FROM
                 *                      billing."DevReadingType" w_1
                 *              WHERE
                 *                      w_1."Name" = billing."TempReading"."ReadingTypeName" AND
                 *                      w_1."DevTypeId" = billing."TempReading"."Devtypeid"
                 *              LIMIT 1
                 *      ),
                 *      "Responsibility" = (
                 *              SELECT
                 *                      w_2."Responsibility"
                 *              FROM
                 *                      billing."DevReadingType" w_2
                 *              WHERE
                 *                      w_2."Name" = billing."TempReading"."ReadingTypeName" AND
                 *                      w_2."DevTypeId" = billing."TempReading"."Devtypeid"
                 *              LIMIT 1
                 *      )
                 */

                #endregion
                #endregion

                #region Source is join of two tables (error build)
                var devs = db.Devices.Join(db.Devtypes, d => d.Devtypeid, dt => dt.Devtypeid, (d, dt) => new { d, dt });

                db.TempReadings
                .Set(t => t.Devid, u => devs.Where(w => w.d.Sernum == u.DevSerNum && w.dt.GlobalType == u.DevGlobalType).Select(s => s.d.Devid).FirstOrDefault())
                .Set(t => t.Devtypeid, u => devs.Where(w => w.d.Sernum == u.DevSerNum && w.dt.GlobalType == u.DevGlobalType).Select(s => s.dt.Devtypeid).FirstOrDefault())
                .Update();

                #region Sql error resault
                #region build sql

                /*
                 * update
                 * billing."TempReading"
                 * set
                 * devid = (
                 * select
                 * s_1.devid
                 * from
                 * (
                 * select
                 *      t1.devtypeid,
                 *      t1.sernum,
                 *      t1.devid
                 * from
                 *      billing.devices t1 ) s_1
                 * inner join (
                 * select -- error select wrapper
                 *      dt_1.devtypeid
                 * from
                 *      (
                 *      select -- in correct variant needs only this one
                 *              t2.devtypeid,
                 *              t2."GlobalType"
                 *      from
                 *              billing.devtypes t2 ) dt_1 ) t3 on
                 * s_1.devtypeid = t3.devtypeid
                 * where
                 * s_1.sernum = billing."TempReading"."DevSerNum"
                 * and dt_1."GlobalType" = billing."TempReading"."DevGlobalType"
                 * limit 1 ),
                 * "Devtypeid" = (
                 * select
                 * dt_2.devtypeid
                 * from
                 * (
                 * select
                 *      t4.devtypeid,
                 *      t4.sernum
                 * from
                 *      billing.devices t4 ) s_2
                 * inner join (
                 * select --error select wrapper
                 *      dt_2.devtypeid
                 * from
                 *      (
                 *      select -- in correct variant needs only this one
                 *              t5.devtypeid,
                 *              t5."GlobalType"
                 *      from
                 *              billing.devtypes t5 ) dt_2 ) t6 on
                 * s_2.devtypeid = t6.devtypeid
                 * where
                 * s_2.sernum = billing."TempReading"."DevSerNum"
                 * and dt_2."GlobalType" = billing."TempReading"."DevGlobalType"
                 * limit 1 )
                 */
                #endregion // build sql

                #region exception

                /*
                 * Error
                 *  Exception: Npgsql.PostgresException
                 *  Message  : 42P01: таблица "dt_1" отсутствует в предложении FROM
                 *     в Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
                 *  --- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
                 *     в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
                 *     в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
                 *     в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
                 *     в Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
                 *  --- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
                 *     в Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
                 *  --- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
                 *     в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
                 *     в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
                 *     в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
                 *     в Npgsql.NpgsqlConnector.<ReadExpecting>d__154`1.MoveNext()
                 *  --- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
                 *     в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
                 *     в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
                 *     в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
                 *     в Npgsql.NpgsqlDataReader.<NextResult>d__32.MoveNext()
                 *  --- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
                 *     в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
                 *     в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
                 *     в Npgsql.NpgsqlDataReader.NextResult()
                 *     в Npgsql.NpgsqlCommand.<Execute>d__71.MoveNext()
                 *  --- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
                 *     в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
                 *     в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
                 *     в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
                 *     в Npgsql.NpgsqlCommand.<ExecuteNonQuery>d__84.MoveNext()
                 *  --- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
                 *     в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
                 *     в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
                 *     в Npgsql.NpgsqlCommand.ExecuteNonQuery()
                 *     в LinqToDB.Data.DataConnection.ExecuteNonQuery() в C:\projects\linq2db\Source\LinqToDB\Data\DataConnection.cs:строка 1114
                 */
                #endregion // exception

                #region correct sql looks like this

                /*
                 * update
                 *      billing."TempReading"
                 * set
                 *      devid = (
                 *      select
                 *              s_1.devid
                 *      from
                 *              (
                 *              select
                 *                      t1.devtypeid,
                 *                      t1.sernum,
                 *                      t1.devid
                 *              from
                 *                      billing.devices t1 ) s_1
                 *      inner join (
                 *                      select
                 *                              t2.devtypeid,
                 *                              t2."GlobalType"
                 *                      from
                 *                              billing.devtypes t2 ) dt_1  on
                 *              s_1.devtypeid = dt_1.devtypeid
                 *      where
                 *              s_1.sernum = billing."TempReading"."DevSerNum"
                 *              and dt_1."GlobalType" = billing."TempReading"."DevGlobalType"
                 *      limit 1 ),
                 *      "Devtypeid" = (
                 *      select
                 *              dt_2.devtypeid
                 *      from
                 *              (
                 *              select
                 *                      t4.devtypeid,
                 *                      t4.sernum
                 *              from
                 *                      billing.devices t4 ) s_2
                 *      inner join (
                 *                      select
                 *                              t5.devtypeid,
                 *                              t5."GlobalType"
                 *                      from
                 *                              billing.devtypes t5 ) dt_2  on
                 *              s_2.devtypeid = dt_2.devtypeid
                 *      where
                 *              s_2.sernum = billing."TempReading"."DevSerNum"
                 *              and dt_2."GlobalType" = billing."TempReading"."DevGlobalType"
                 *      limit 1 )
                 */
                #endregion // correct sql

                #endregion // Sql error resault
                #endregion // Source is join of two tables
            }

            Console.ReadKey();
        }
Esempio n. 2
0
        public static void Test2()
        {
            using (var db = new ExDbModel())
            {
                try
                {
                    //db.CreateTable<billing_Devtype>();
                    //db.CreateTable<billing_Device>();
                    //db.CreateTable<billing_DevReadingType>();
                    //db.CreateTable<billing_TempReading>();
                    //db.CreateTable<billing_TestEntity>();
                    db.CreateTable <billing_TestBaseEntity>();
                    db.CreateTable <billing_TestChEntity>();
                } catch { }

                DataConnection.TurnTraceSwitchOn();
                DataConnection.WriteTraceLine = (s, s1) => { Console.WriteLine(s); };

                var pe = new billing_TestBaseEntity[] {
                    new billing_TestBaseEntity {
                        Id = Guid.NewGuid(), Name = "T1"
                    },
                    new billing_TestBaseEntity {
                        Id = Guid.NewGuid(), Name = "T2"
                    },
                    new billing_TestBaseEntity {
                        Id = Guid.NewGuid(), Name = "T3"
                    },
                };
                db.BulkCopy(_bulkCopyOptions, pe);
                var ce = new billing_TestChEntity[] {
                    new billing_TestChEntity {
                        Id = Guid.NewGuid(), BaseId = pe[0].Id, Name = "ToT1"
                    },
                    new billing_TestChEntity {
                        Id = Guid.NewGuid(), BaseId = pe[1].Id, Name = "ToT2"
                    },
                };
                db.BulkCopy(_bulkCopyOptions, ce);

                var group  = db.TestBaseTable.GroupJoin(db.TestChTable, dt => dt.Id, d => d.BaseId, (dt, d) => new { dt, d });
                var smany1 = group.SelectMany(sm => sm.d.DefaultIfEmpty(), (sm, d) => new { sm.dt, d });
                //var smany2 = db.Devtypes.GroupJoin(db.Devices, dt => dt.Devtypeid, d => d.Devtypeid, (dt, d) => new { dt, d}).SelectMany(sm => sm.d.DefaultIfEmpty(), (sm, d) => new { sm.dt, d });

                //var g2 =
                //    from b in db.TestBaseTable
                //    from c in db.TestChTable.LeftJoin(l => l.BaseId == b.Id)
                //    select new { b, c };

                var g2 =
                    from b in db.Gw
                    from c in db.GwSet.LeftJoin(l => l.GatewayId == b.Id)
                    select new { b, c };

                var g3 = db.Gw.SelectMany(b => db.GwSet.Where(l => l.GatewayId == b.Id).DefaultIfEmpty(), (b, c) => new { b, c });

                var q1 = smany1.ToList();
                //var q2 = g2.ToList();
                var q3 = g3.ToList();

                //Console.WriteLine(string.Join("\n", db.Homes.ToList()));
                //
                //var typId1s = db.Devices.Select(s => s.Devtypeid).AsCte("CTE_1");
                //var typId2s = typId1s.Distinct().AsCte("CTE_2");
                //var typId3s = typId2s.Distinct().AsCte("CTE_3");
                //var typId4s = typId3s.Distinct().AsCte("CTE_4");
                //
                //typId1s.Count();

                //var qCte = db.Devtypes.Where(w => w.Devtypeid.NotIn(typId2s)).ToList();

                //var d = new billing_Device
                //{
                //    Devid = Guid.NewGuid(),
                //    Sernum = "Test01",
                //    Devtypeid = 1
                //};
                //
                //db.Devices.Insert(() => new billing_Device
                //{
                //    Devid = Guid.NewGuid(),
                //    Sernum = "Test01",
                //    Devtypeid = 1
                //});

                //db.BeginTransaction();

                //
                //db.Devtypes.Insert(() => new billing_Devtype { Typename = "TestType1", GlobalType = 1 });
                //db.Devtypes.Insert(() => new billing_Devtype { Typename = "TestType2", GlobalType = 1 });
                //db.Devtypes.Insert(() => new billing_Devtype { Typename = "TestType3", GlobalType = 1 });
                //db.Devtypes.Insert(() => new billing_Devtype { Typename = "TestType4", GlobalType = 1 });
                //try
                //{
                //    db.BulkCopy(_bulkCopyOptions, new billing_TestEntity[] { new billing_TestEntity { Id = Guid.NewGuid(), Name = "T1" } });
                //    db.Insert(new billing_TestEntity { Id = Guid.NewGuid(), Name = "T1" });
                //}
                //catch (Exception e)
                //{
                //    Console.WriteLine(e.Message);
                //}

                //
                //var o3 =4;
                //
                //var rnd = new Random();
                //var range = Enumerable.Range(0, 500000).Select(s => new billing_Device
                //{
                //    Devid = Guid.NewGuid(),
                //    Sernum = "Test01",
                //    Devtypeid = rnd.Next(1, o3) * 2 - 1
                //});
                //
                //db.BulkCopy(_bulkCopyOptions, range);
                //
                //var group = db.Devtypes.GroupJoin(db.Devices, dt => dt.Devtypeid, d => d.Devtypeid, (dt, d) => new { dt, d = d.DefaultIfEmpty() });
                //var smany1 = group.SelectMany(sm => sm.d.Select(d => new { sm.dt, d }));
                //var smany2 = db.Devtypes.GroupJoin(db.Devices, dt => dt.Devtypeid, d => d.Devtypeid, (dt, d) => new { dt, d}).SelectMany(sm => sm.d.DefaultIfEmpty(), (sm, d) => new { sm.dt, d });
                //
                //var q1 = smany2.ToList();
                //var q2 = db.Devtypes.GroupJoin(db.Devices, dt => dt.Devtypeid, d => d.Devtypeid, (dt, d) => new { dt, d }).SelectMany(sm => sm.d.DefaultIfEmpty(), (sm, d) => new { sm.dt, d }).Where(w => w.d == null).ToList();


                //var q2d= db.Devtypes
                //    .GroupJoin(db.Devices.Select(s => (int?) s.Devtypeid).Distinct()
                //        , dt => dt.Devtypeid, d => d, (dt, d) => new { dt, d })
                //    .SelectMany(sm => sm.d.DefaultIfEmpty(), (sm, d) => new { sm.dt, d })
                //    .Where(w => w.d == null)
                //    .ToList();
                //
                //var q3 = db.Devtypes.Where(w => !db.Devices.Select(s => s.Devtypeid).Contains(w.Devtypeid)).ToList();
                //var q4 = db.Devtypes.Where(w => !db.Devices.Any(a=>a.Devtypeid == w.Devtypeid)).ToList();
                //var qNotIN = db.Devtypes.Where(w => w.Devtypeid.NotIn(db.Devices.Select(s => s.Devtypeid).Distinct())).ToList();
                //var qIN = db.Devtypes
                //    .Where(w => w.Devtypeid.In(
                //        db.Devices.Select(s => s.Devtypeid).Distinct()
                //    )).ToList();

                //var typIds = db.Devices.Select(s => s.Devtypeid).AsCte("c1");
                //var typId2s = typIds.Distinct().AsCte("c2");
                //
                //var qCte = db.Devtypes.Where(w => w.Devtypeid.NotIn(typId2s)).ToList();
                //var qCte2 = db.Devtypes.Where(w => !cte.Contains(w.Devtypeid)).ToList();


                //var gb = db.Devtypes.GroupBy(g => g.GlobalType).Select(s => new { s.Key, count = s.Count(), max = s.Select(s1 => s1.Typename).Max() }).ToList();

                //db.Devices.Where(w => w.Sernum == "Test01")
                //    .Set(s => s.Devtype, new billing_Devtype { Typename = "TestType3", GlobalType = 1 })
                //    .Update()
                //;
            }
        }