Skip to Main Content
Forums

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Entity Framework Oracle Timestamp

Selim YILDIZSep 2 2019 — edited Sep 11 2019

We have table named T_SC_SERVICE has INSTANCE_ID column which is type Timestamp(6).

I'm using Entity Framework 6 and DB first approaches.

I'am trying to Add and Select item for this table with using LINQ.

Insert with LINQ as shown below:

Service newItem = new Service()
{
    InstanceId = DateTime.Now,
};
this.ObjectSet.Add(newItem);
this.SaveChanges();

That LINQ generates SQL as below. As you can see INSTANCE_ID parameter send as DateTime as expected.

insert into "DGARSMART"."T_SC_SERVICE"("INSTANCE_ID")
values (:p0)
-- :p0: '29.08.2019 07:33:38' (Type = DateTime)
-- Executing at 29.08.2019 07:33:38 +03:00
-- Completed in 66 ms with result: 1

Here is my Problem:

SELECT with LINQ as shown below:

       

internal Service GetServiceByInstanceId(DateTime instanceId)

{

return this.ObjectSet.FirstOrDefault(i => i.InstanceId == instanceId);

}

That LINQ generates SQL as below. As you can see Instance_ID send as Date not DateTime. So it always return Null. This is the same entity object and same model. I could not figure out why this LINQ is sending DateTime as Type of Date instead of DateTime.

SELECT 
"Extent1"."INSTANCE_ID" AS "INSTANCE_ID", 
FROM "DGARSMART"."T_SC_SERVICE" "Extent1"
WHERE (("Extent1"."INSTANCE_ID" = :p__linq__0) AND (:p__linq__0 IS NOT NULL)) AND (ROWNUM <= (1) 
-- p__linq__0: '29.08.2019 07:33:38' (Type = Date)
-- Executing at 29.08.2019 07:34:47 +03:00
-- Completed in 5 ms with result: OracleDataReader

I'am using these packages:

 <package id="Oracle.ManagedDataAccess" version="12.2.1100" targetFramework="net45" />
 <package id="Oracle.ManagedDataAccess.EntityFramework" version="12.2.20190115" targetFramework="net45" />
 <package id="EntityFramework" version="6.0.0" targetFramework="net45" />

Comments

user2309906
HI ...you can use connect_by_root to find all the id's IN a hierarchy ...to find the one's NOT IN hirarachy should not be a problem from there ... any sample data ???
BluShadow
user13304081 wrote:
hye all,

my aim is to retrieve all the positions that are not in a hierarchy...please help me ...
i
What is your database version?
How about providing some create table statements and example data (insert statements) for us?
How about showing what output you would expect from that example data?
Frank Kulash
Hi,

Welcome to the forum!

It might be more efficient to do a CONNECT BY query, and find the nodes that are leaves at the top level: 
SELECT	*
FROM	table_x
WHERE	CONNECT_BY_ISLEAF	= 1
AND	LEVEL			= 1
START WITH	parent_id	IS NULL
CONNECT BY	parent_id	= PRIOR id
	AND	LEVEL		<= 2	-- For efficiency
;
Whenever you have a problem, post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data.
It helps to mention which version of Oracle you're using, especially when asking about CONNECT BY queries, where every version since Oracle 7 has had significant changes.
BluShadow
Or something like this...
SQL> ed
Wrote file afiedt.buf

  1  with t as (select null as mgr, 1 as emp_id, 'Chairman' as position from dual union all
  2             select 1, 2, 'Vice Chairman' from dual union all
  3             select 2, 3, 'Company Director' from dual union all
  4             select 3, 4, 'Sales Manager' from dual union all
  5             select 3, 5, 'Technology Manager' from dual union all
  6             select 3, 6, 'HR Manager' from dual union all
  7             select 4, 7, 'Sales Rep' from dual union all
  8             select 4, 8, 'Sales Rep' from dual union all
  9             select 4, 9, 'Sales Rep' from dual union all
 10             select 10, 10, 'Dodgy Contractor' from dual union all
 11             select 5, 11, 'Software Developer' from dual union all
 12             select 5, 12, 'Software Developer' from dual union all
 13             select 5, 13, 'Network Specialist' from dual union all
 14             select 5, 14, 'Communications Implementer' from dual union all
 15             select 6, 15, 'Rectruitment Agent' from dual union all
 16             select 6, 16, 'Job Marketing Agent' from dual union all
 17             select 6, 17, 'Job Marketing Assistant' from dual)
 18  --
 19  -- END OF TEST DATA
 20  --
 21  select emp_id from t
 22  minus
 23  select emp_id
 24  from   t
 25  connect by mgr = prior emp_id
 26* start with mgr is null
SQL> /

    EMP_ID
----------
        10

SQL>
... but it does depend what the data looks like and what exactly you are trying to achieve.
Nimish Garg
TRY THIS
SELECT 
	EMPNO 
FROM 
	SCOTT.EMP 
WHERE 
	EMPNO NOT IN 
	(
		SELECT 
			EMPNO 
		FROM 
			SCOTT.EMP 
		CONNECT BY PRIOR 
			EMPNO=MGR 
		START WITH MGR = 7566
	)
1 - 5

Post Details

Added on Sep 2 2019
#odp-net
3 comments
1,118 views