New version, but still error.

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
dqminh
Posts: 28
Joined: Wed 12 Nov 2008 01:31

New version, but still error.

Post by 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

dqminh
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Post by 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.

Post by 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

dqminh
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Post by 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)
                   }
               );

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 24 Feb 2009 15:31

These problems are tightly related. I will let you know as soon as the issue is fixed.

dqminh
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Post by dqminh » Wed 25 Feb 2009 03:37

And another problem: dotConnect current not support compare DateTime? with null (long? with null is work)

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 25 Feb 2009 12:25

Problems with DateTime and Group By are fixed. Look forward to the next build.

dqminh
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Post by 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()
};

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 26 Feb 2009 15:35

This problem also was fixed.
We appreciate your help.

dqminh
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Post by dqminh » Tue 03 Mar 2009 02:46

Hi, is dotConnect support Transaction? I cannot find it.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by 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.

dqminh
Posts: 28
Joined: Wed 12 Nov 2008 01:31

Post by 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.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

Post Reply