New version, but still error.

New version, but still error.

Postby dqminh » Thu 19 Feb 2009 04:35

Hi,

In version 5.0.0.22, still urgent bug:

We have two tables parent and child with relation ship. All parent and child auto generated ID from trigger. Script below:

Code: Select all
CREATE TABLE v2_data_File(
    File_Id       NUMBER(10, 0)     NOT NULL,
    Message_Id    NUMBER(10, 0)     NOT NULL,
    File_Name     NVARCHAR2(128)    NOT NULL,
    Body          LONG RAW          NOT NULL,
    CONSTRAINT v2_PK28 PRIMARY KEY (File_Id)
)
;


CREATE TABLE v2_data_Message(
    Message_Id      NUMBER(10, 0)     NOT NULL,
    Parent_Id       NUMBER(10, 0),
    Body            NVARCHAR2(512)    NOT NULL,
    Created_Date    TIMESTAMP(6)      NOT NULL,
    Created_By      NVARCHAR2(256)    NOT NULL,
    Flag            CHAR(1)           NOT NULL,
    CONSTRAINT v2_PK01 PRIMARY KEY (Message_Id)
)
;


Using Entity developer to gen C# code, with MessageId and FileId both auto generated value, and auto-sync on insert. So when insert entity to database, exception raise:

Code: Select all
BasicDataContext db = new BasicDataContext();
Message msg1 = new Message() { Body = DateTime.Now.ToLongDateString(), CreatedBy = "admin1", CreatedDate = DateTime.Now, Flag = "S" };
Message msg2 = new Message() { Body = DateTime.Now.ToLongDateString(), CreatedBy = "admin2", CreatedDate = DateTime.Now, Flag = "S" };
//db.Messages.InsertAllOnSubmit(new Message[] { msg1, msg2 });
db.Messages.InsertOnSubmit(msg2);
db.SubmitChanges();

File file = new File { Body = new byte[] { 0, 1, 2 }, FileName = "abc", Message = msg2 };
db.Files.InsertOnSubmit(file);
db.SubmitChanges(); // Value of member 'FileId' of an object of type 'File' changed.A member that is computed or generated by the database cannot be changed.


Please check it.
dqminh
 
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Postby AndreyR » Thu 19 Feb 2009 09:18

LINQ to Oracle inserts detail records when the parent record is inserted, this behavior causes the exception. Please change the code like following:
Code: Select all
        V2_DATA_MESSAGE msg2 = new V2_DATA_MESSAGE()
        {
          BODY = DateTime.Now.ToLongDateString(),
          CREATED_BY = "admin2",
          CREATED_DATE = DateTime.Now,
          FLAG = "S"
        };
        V2_DATA_FILE file = new V2_DATA_FILE
        {
          BODY = new byte[] { 0, 1, 2 },
          FILE_NAME = "abc",
          V2_DATA_MESSAGE = msg2
        };
        db.V2_DATA_FILEs.InsertOnSubmit(file);
        db.SubmitChanges();

The result code will insert both file and message without exceptions.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby dqminh » Thu 19 Feb 2009 10:39

Ok, thanks so much, it work :D
dqminh
 
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Group with paging is error.

Postby dqminh » Thu 19 Feb 2009 12:54

Yes, if I use the group by, then after that paging, it error.

Code: Select all
var query = db.OrderDetails
                .GroupBy(p => p.PartCode)
                .Select(g => new InShortSupplier()
                    {
                        PartCode = g.Key 
                    });

return query.Skip(startRowIndex).Take(maximumRows);


Oracle error: ORA-00979: not a GROUP BY expression

The query in dbmonitor:

Execute: SELECT t1."PartCode"
FROM (
SELECT t2.PART_CODE AS "PartCode", ROWNUM AS rnum
FROM VDMS.V2_P_ORDER_DETAIL t2
GROUP BY t2.PART_CODE
) t1
WHERE (t1.rnum > :p0) AND (t1.rnum <= :p1)

I think you must enclose with a new select outside this sql.
dqminh
 
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Postby AndreyR » Thu 19 Feb 2009 15:36

Thank you for the report, we have reproduced the problem.
I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby dqminh » Fri 20 Feb 2009 06:59

And another bug: in the group by expression, if select only field, that ok, but if add the aggregate, the sql command is not valid:

OK:

Code: Select all
var query = db.OrderDetails
               .GroupBy(p => p.PartCode)
               .Select(g => new InShortSupplier()
                   {
                       PartCode = g.Key
                   }
               );



Error:

Code: Select all
var query = db.OrderDetails
               .GroupBy(p => p.PartCode)
               .Select(g => new InShortSupplier()
                   {
                       PartCode = g.Key,
                       TotalOrder = g.Sum(p => p.OrderQuantity)
                   }
               );
dqminh
 
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Postby AndreyR » Tue 24 Feb 2009 15:31

These problems are tightly related. I will let you know as soon as the issue is fixed.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby dqminh » Wed 25 Feb 2009 03:37

And another problem: dotConnect current not support compare DateTime? with null (long? with null is work)
dqminh
 
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Postby AndreyR » Wed 25 Feb 2009 12:25

Problems with DateTime and Group By are fixed. Look forward to the next build.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby dqminh » Wed 25 Feb 2009 15:17

Ok, and another bug :D

If we have a query (Q1) that return a list of object, and in object result has a property that is a result of another query (Q2), then if in Q2 has more than one where condition, the devart component throw the exception (a bug).

OK:

Code: Select all
var query = from h in db.OrderHeaders
where (h.Status == OrderStatus.OrderConfirmed || h.Status == OrderStatus.OrderSent) && h.DealerCode == UserHelper.DealerCode
orderby h.OrderDate
select new
{
   h.OrderHeaderId,
   h.OrderDate,
   Items = from d in db.OrderDetails
           join p in db.Parts on d.PartCode equals p.PartCode
           where d.OrderHeaderId == h.OrderHeaderId // one condition
           select new
           {
               d.PartCode,
               d.OrderQuantity
           },
   Total = (from d in db.OrderDetails
          where d.OrderHeaderId == h.OrderHeaderId
          select d.QuotationQuantity * d.UnitPrice).Sum()
};


NOT OK:

Code: Select all
var query = from h in db.OrderHeaders
where (h.Status == OrderStatus.OrderConfirmed || h.Status == OrderStatus.OrderSent) && h.DealerCode == UserHelper.DealerCode
orderby h.OrderDate
select new
{
   h.OrderHeaderId,
   h.OrderDate,
   Items = from d in db.OrderDetails
           join p in db.Parts on d.PartCode equals p.PartCode
           where d.OrderHeaderId == h.OrderHeaderId && p.DatabaseCode == "HTF" // two conditions
           select new
           {
               d.PartCode,
               d.OrderQuantity
           },
   Total = (from d in db.OrderDetails
          where d.OrderHeaderId == h.OrderHeaderId
          select d.QuotationQuantity * d.UnitPrice).Sum()
};
dqminh
 
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Postby AndreyR » Thu 26 Feb 2009 15:35

This problem also was fixed.
We appreciate your help.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby dqminh » Tue 03 Mar 2009 02:46

Hi, is dotConnect support Transaction? I cannot find it.
dqminh
 
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Postby Shalex » Tue 03 Mar 2009 12:12

The application creates an OracleTransaction object by calling BeginTransaction on the OracleConnection object. All subsequent operations associated with the transaction (for example, committing or aborting the transaction), are performed on the OracleTransaction object.
The correlation between OracleConnection and OracleTransaction is always 1:1. Our documentation contains an example of using it in the appropriate section (OracleTransaction Class).

The LINQ queries can be executed inside TransactionScope. LINQ and Entity Framework queries contain the transaction in itself.
Shalex
Devart Team
 
Posts: 7833
Joined: Thu 14 Aug 2008 12:44

Postby dqminh » Tue 03 Mar 2009 15:27

Sorry, I still cannot find. Could you show me the example that demo the transaction with LINQ? Thanks.
dqminh
 
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Postby AndreyR » Wed 04 Mar 2009 09:26

In fact, when you call SubmitChanges(), LINQ opens a transaction for you, and if everything goes ok, it is commited,
and in case any exception occurs the transaction is rolled back.
But you stil can use TransactionScope, like in the following example:
Code: Select all
      TransactionOptions to = new TransactionOptions();
      to.IsolationLevel = IsolationLevel.ReadCommitted;
      using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, to)) {
        using (DataContext1 db = new DataContext1()) {
          Foo st1 = new Foo
          {
            Name = "test1",
            Info = "test1"
          };
          Foo st2 = new Foo
          {
            Name = "test2",
            Info = "test2"
          };
          db.Foos.InserAlltOnSubmit(new Foo[]{st1, st2});
          db.SubmitChanges();
          trans.Complete();
        }
      }

In this case LINQ will use your transaction instead of creating a new one.
Also, DataContext contains the Transaction property, and you can assign it to your Transaction object.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Next

Return to LinqConnect (LINQ to SQL support)