Devart Forums

Mac

I have an oracle db running our ERP system which uses the concept of shared tables (not my choice!)

I can only login with a user 'query' to a query schema. This user is given read-only permissions to other schemas and is the only means of accessing data in these other schemas.

I access various sprocs/functions etc in my query schema as it's the only schema which I can modify, but it has no tables or data. They exist in the other schemas.

Every schema apart from Query has identical sets of tables.

schemaA.Table1
schemaB.Table1
query.MySproc

A "shared table" means that the data is only in schemaA even though empty tables exist in the other schemas. I can only access the data by qualifying shared table with the schema name.

It means I often end generating queries like

Code: Select all

from table1 in context.GetData<Table1>()
join table2 in context.GetData<Table2>() on table1.col1 equals table2.col1
join table3 in context.GetData<Table3>() on table2.col2 equals table3.col2

which geneates pl/sql

Code: Select all

select (..fields)
from schemaA.table1 t1
inner join schemaB.table2 t2 on t1.col1 = t2.col1
inner join schemaA.table3 t3 on t2.col2 = t3.col2

I can never know the specific schema I am targeting for non shared data until runtime as it is based on information associated with a ERP user.

How can I set the schema per table at runtime based on some business logic per user?

Previously I modified the T4 templates to alter the

Code: Select all

[Table(Name = "SharedPrefix.TableA")]

which would work if I could set the default schema and not prefix the TableAttributes.

I now have to use three different schemas query, sharedTableSchema, nonsharedTableSchema all from the same dataContext.

Code: Select all

TableAttribute

is sealed so cannot override, so is

Code: Select all

AttributeMappingSource

. Haven't been able to inherit from

Code: Select all

MappingSource

to override the metamodel, type resolution.

Currently I'm running a static method to prefix TableAttribute names in the T4 templates and I've overloaded the datacontext constructors to accept the schema name and am running a sql command

Code: Select all

ALTER SESSION SET CURRENT_SCHEMA=

when connecting.

Looking at ways to use IoC interception also to try and inject the schema or change the mapping at runtime but its all a bit hacky and would be so much easier to have the means to map each table using runtime instead of compile time logic.

Maybe I've missing something obvious in the code but when it comes to my admittedly very specific scenario, it's is really awkward to get your product to work well.

Any ideas or thoughts on providing an extensibility point to make this possible?

Mac

Currently using dotConnect for Oracle 8.5.521.0

I cannot find any documentation or examples of the method signature that actually uses parameters.

Code: Select all

IEnumerable<TResult> ExecuteQuery<TResult>(string query, params object[] parameters);

In particular I am interested in using bind variables if possible. Something like

Code: Select all

context.ExecuteQuery("select colA from tableA where colA = :paramA", "paramAValue");

or do I have to use argument placeholders like:

Code: Select all

context.ExecuteQuery("select colA from tableA where colA = '{0}'", "paramAValue");

If its arg placeholder style, what protection does this offer for sql injection attacks?
none, or is the param value internally formatted to avoid malicious values?

...or do I pass OracleParameter objects

Code: Select all

context.ExecuteQuery("select colA from tableA where colA = :paramA", new OracleParameter("paramA",  OracleDbType.NVarChar, 10){ Value = "paramAValue" });

Documentation and examples around this seem spectacularly poor and none of the above examples seem to work. Please advise or update documentation for ExecuteQuery and Parameters.

Mac

Thanks for the reply,

I think I'll wait though, the main reason for upgrading to this version is the template and template editor improvements.

Mac

I have the same issue upgrading DevArt to version 6.30.160.0 and it is not practical to change all the existing code to use anonymous types. When will this build be available?

StanislavK wrote:Thank you for the report. We have reproduced and fixed this problem. The fix will be available in the nearest build.

As a temporary workaround, you can, e.g., use an anonymous type, and then convert the collection to a list of clsDropDownElement's:
Code: Select all
var lv_result = (from c in lv_context.clsValidCustomerGroups 
                 select new  
                 { 
                   ID = c.CustomerGroupId, 
                   Name = c.CustomerGroupName 
                 })
                 .OrderBy(p => p.Name)
                 .Select(a => new DTO.clsDropDownElement() 
                   { ID = a.ID, Name = a.Name })
                 .ToList();
Please tell us if this helps.

Mac

Andrey

Thanks for reply. I've manage to work around this problem for some time but now I'm experiencing it again. The solution with materialising list does not work.

Here is the code I'm executing:

Code: Select all

            var productCodes = new List(products.Select(x => x.ProductCode)).ToList();

            var descriptions = (from cmlong in oneOfficeData.Cmlongs
                               where productCodes.Contains(cmlong.CmdesProduct)
                               select new { cmlong.CmdesProduct, cmlong.CmdesSeq, cmlong.CmdesDesc }).ToList();

Mac

I'm having problems with converting query that has "not in" subquery to Linq object. The query is

Code: Select all

SELECT * FROM MyTable
WHERE MyID NOT IN (SELECT ID FROM MyOtherTable)

In LinqToSQL it should look like:

Code: Select all

var q = from t1 in MyTable
        let t2s = from t2 in MyOtherTable
                  select t2.ID
        where !t2s.Contains(t1.MyID) 
        select t1;

However it returns all records from MyTable.
I've tried to split it in 2 different queries:

Code: Select all

var mySet = from t2 in MyOtherTable select t2.ID;
var q = from t1 in MyTable
        where !mySet.Contains(t1.MyID) 
        select t1;

the first query returns correct result but the second is still ignoring the "where !contains" condition.

The same thing happen with both .Contains() and .Any()

Is there any way to get "not in" subquery in LinqToOracle?

Mac

Hi

We have problem with selecting new struct object from linq to oracle query. The code below is throwing System.ArgumentNullException: Value cannot be null. Parameter name: constructor where Address is a struct.

Code: Select all

var test1 = from stloc in oneOfficeData.Stlocs
                            .DefaultIfEmpty()
                        from ndmas in oneOfficeData.Ndmas
                            .Where(x => x.NdmNdcode == stloc.StlocNdcode)
                            .DefaultIfEmpty()
                        select new
                        {
                            Address = new Address
                                                  {
                                                      Address1 = ndmas.NdmAddr1 ?? string.Empty,
                                                      Address2 = ndmas.NdmAddr2 ?? string.Empty,
                                                      Address3 = ndmas.NdmAddr3 ?? string.Empty,
                                                      Address4 = ndmas.NdmAddr4 ?? string.Empty,
                                                      Address5 = ndmas.NdmAddr5 ?? string.Empty,
                                                      Country = ndmas.NdmCountry ?? string.Empty,
                                                      Postcode = ndmas.NdmPostcode ?? string.Empty
                                                  }
                        }.ToList();

Stack trace:
at System.Linq.Expressions.Expression.New(ConstructorInfo constructor, IEnumerable`1 arguments)
at System.Linq.Expressions.Expression.New(ConstructorInfo constructor, Expression[] arguments)
at Devart.Data.Linq.Provider.h.a(bk A_0)
at Devart.Data.Linq.Provider.g.a(bk A_0)
at Devart.Data.Linq.Provider.h.a(SqlExpression A_0)
at Devart.Data.Linq.Provider.h.a(bk A_0)
at Devart.Data.Linq.Provider.g.a(bk A_0)
at Devart.Data.Linq.Provider.h.a(SqlExpression A_0)
at Devart.Data.Linq.Provider.h.a(SqlExpression A_0, Boolean A_1)
at Devart.Data.Linq.Provider.m.a(u A_0, SqlExpression A_1, Boolean A_2)
at Devart.Data.Linq.Provider.m.a(Type A_0, SqlExpression A_1, u A_2)
at Devart.Data.Linq.Provider.DataProvider.CompiledQuery.GetReaderFactory(List`1 elementInstanceTypes, u services, SqlNode query)
at Devart.Data.Linq.Provider.DataProvider.CompiledQuery..ctor(QueryInfo queryInfo, u services, Boolean isQueryObjectByKey, Object queryObjectKey)
at Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression query)
at Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Compile(Expression query)
at Devart.Data.Linq.DataQuery`1.i()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList(IEnumerable`1 source)

Mac

Will do thanks.

Having a problem getting an assembly reference to work.

It seems that if I I add a project reference it isn't picked up by the template.

The only way I can get to not throw an error so far is by putting the whole file path to the dll (without the dll extension)

ie

gives a assembly not found error

Adding a file reference path of the external library dll location on the project properties where the template is located also isn't picking it up

only the following doesn't throw an error

I still can't add the namespace though

throws a MyOther.Project namespace not found.

Any ideas/examples?

Mac

Is it possible to use T4 include files in the model templates?

eg.

Thanks
Mac

Mac

AndreyR wrote:You can make a simple change to the template in order to remove schema from the generated code.
Replace the
Code: Select all
[Table(Name = @"")]
line with this one:
Code: Select all
[Table(Name = @"")]
In case you need to change the current schema try to execute the following code:
Code: Select all
ALTER SESSION SET CURRENT_SCHEMA = "Schema1"

I cannot find this template, where is it normally stored?

Mac

I'm currently evaluating the trial copy (5.25.39.0) which I downloaded on Monday last week.

I'll send the generated DataContext file as you suggest though, if you need any further information just let me know.

Mac

Hi, I'm running the following linq query against an Oracle 9.2 database

var query = from c in Context.Customers
join ca in Context.CustomerAddress on c.DlcusCustomer equals ca.SmAccount
join a in Context.Address on ca.SmNacode equals a.NdmNdcode
where c.DlcusCode1 == "Y" && a.NdmAdcha1 == "Y"
select new Model.CustomerAddress
{
Code = ca.Address.NdmNdcode,
Email = ca.Address.NdmEmail,
Fax = ca.Address.NdmTelex,
Telephone = ca.Address.NdmTelephone,
Name = ca.Address.NdmName,
IsInvoice = Convert.ToBoolean((c.DlcusAppacc == a.NdmNdcode) ? 1 : 0),
Address =
new Model.Address
{
Address1 = ca.Address.NdmAddr1,
Address2 = ca.Address.NdmAddr2,
Address3 = ca.Address.NdmAddr3,
Address4 = ca.Address.NdmAddr4,
Address5 = ca.Address.NdmAddr5,
Country = ca.Address.NdmCountry,
Postcode = ca.Address.NdmPostcode
}
};

return query.ToList();

The query is generating the following pl/sql

SELECT t15.NDM_NDCODE AS "NdmNdcode", t14.NDM_NAME AS "NdmName", t13.NDM_ADDR1 AS "NdmAddr1", t12.NDM_ADDR2 AS "NdmAddr2", t11.NDM_ADDR3 AS "NdmAddr3", t10.NDM_ADDR4 AS "NdmAddr4", t9.NDM_ADDR5 AS "NdmAddr5", t8.NDM_POSTCODE AS "NdmPostcode", t7.NDM_COUNTRY AS "NdmCountry", t6.NDM_TELEPHONE AS "NdmTelephone", t5.NDM_TELEX AS "NdmTelex", t4.NDM_EMAIL AS "NdmEmail",
(CASE
WHEN t1.DLCUS_APPACC = t3.NDM_NDCODE THEN :p1
ELSE :p2
END) AS C1
FROM MACL.DLCUST t1
INNER JOIN MACL.SMDEL t2 ON t1.DLCUS_CUSTOMER = t2.SM_ACCOUNT
INNER JOIN MACL.NDMAS t3 ON t2.SM_NACODE = t3.NDM_NDCODE
INNER JOIN MACL.NDMAS t4 ON t2.SM_NACODE = t4.NDM_NDCODE
INNER JOIN MACL.NDMAS t5 ON t2.SM_NACODE = t5.NDM_NDCODE
INNER JOIN MACL.NDMAS t6 ON t2.SM_NACODE = t6.NDM_NDCODE
INNER JOIN MACL.NDMAS t7 ON t2.SM_NACODE = t7.NDM_NDCODE
INNER JOIN MACL.NDMAS t8 ON t2.SM_NACODE = t8.NDM_NDCODE
INNER JOIN MACL.NDMAS t9 ON t2.SM_NACODE = t9.NDM_NDCODE
INNER JOIN MACL.NDMAS t10 ON t2.SM_NACODE = t10.NDM_NDCODE
INNER JOIN MACL.NDMAS t11 ON t2.SM_NACODE = t11.NDM_NDCODE
INNER JOIN MACL.NDMAS t12 ON t2.SM_NACODE = t12.NDM_NDCODE
INNER JOIN MACL.NDMAS t13 ON t2.SM_NACODE = t13.NDM_NDCODE
INNER JOIN MACL.NDMAS t14 ON t2.SM_NACODE = t14.NDM_NDCODE
INNER JOIN MACL.NDMAS t15 ON t2.SM_NACODE = t15.NDM_NDCODE
WHERE (t1.DLCUS_CODE1 = :p0) AND (t3.NDM_ADCHA1 = :p0)

ParameterName = p0
DbType = String
Value = Y
ParameterName = p1
DbType = Int32
Value = 1
ParameterName = p2
DbType = Int32
Value = 0

which returns the expected rows when run in oracle sql developer, however the query is throwing the following exception in my unit tests

System.ArgumentNullException: System.ArgumentNullException : Value cannot be null.
Parameter name: constructor
at System.Linq.Expressions.Expression.New(ConstructorInfo constructor, IEnumerable`1 arguments)
at System.Linq.Expressions.Expression.New(ConstructorInfo constructor, Expression[] arguments)
at Devart.Data.Linq.Provider.g.b.a(a0 A_0)
at Devart.Data.Linq.Provider.g.b.a(SqlExpression A_0)
at Devart.Data.Linq.Provider.g.b.a(a0 A_0)
at Devart.Data.Linq.Provider.g.b.a(SqlExpression A_0)
at Devart.Data.Linq.Provider.g.b.b(SqlExpression A_0)
at Devart.Data.Linq.Provider.g.a(d A_0, SqlExpression A_1)
at Devart.Data.Linq.Provider.g.a(Type A_0, SqlExpression A_1, d A_2)
at Devart.Data.Linq.Provider.DataProvider.d.a(Int32 A_0)
at Devart.Data.Linq.Provider.DataProvider.a(d A_0, f A_1, IDbConnection A_2, IDataReader A_3)
at Devart.Data.Linq.Provider.DataProvider.a(d A_0, f A_1, Object[] A_2, Object[] A_3, Object A_4)
at Devart.Data.Linq.Provider.DataProvider.a(d A_0, Object[] A_1)
at Devart.Data.Linq.Provider.DataProvider.h(Expression A_0)
at Devart.Data.Linq.DataQuery`1.i()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Any ideas what might be causing this?

Thanks in advance
Mac.

Devart Forums

Search found 12 matches

set table schema at runtime

Bind variables, parameters and ExecuteQuery

Having the same issue

How to implement NOT IN subquery in LinqToOracle?

Selecting struct object throws System.ArgumentNullException

Can't get assembly references to work

Model Template includes

System.ArgumentNullException