rivantsov / vita Goto Github PK

Hi,
1º
if i use ToUpper in link if fails with the error no such function: UCASE
i was using
if (!session.EntitySet().Any(c => c.ID.ToUpper() == "DZ"))

2º
I am having problems with encrypted data in sql lite
licencaAplicacao.Contribuinte = EntityHelper.GetSession(licencaAplicacao)
.NewOrUpdate(licencaAplicacao.Contribuinte, contribuinte, "TsChannel");
The NewOrUpdate is giving foreignh key errors.

Regards.

One thing we would like to have built into an ORM is support for a Multi-Tenant database?

Multi-Tenant Data Architecture

We use the shared database, shared schema approach. Currently we use a forked version of a relatively obscure micro-ORM that we added basic Multi-Tenant support to so none of our database interactions has to be concerned with the fact that it is Multi-Tenant, it is handled for us automatically.

I have an existing MS SqlServer db, with tables and several views on them. Vita wants to create primary key on the views with an alter table, but it's not possible.

What is the rigth way to do this:
Session.EntitySet().Where(x => x.STARTDATE > new DateTime(2017,1,1));
It gives me a Vita.Entities.DataAccessException with the info
"Conversion failed when converting character string to smalldatetime data type."
STARTDATE is DateTime?

I would really like to use Vita in a standard MVC web application, but am a bit concerned with issues like the session life cycle etc. ( the same issues that we have dealt with in EF ). Unfortunately I cant find anywhere in the documentation where you talk to the session lifecycle and in fact to any mvc controller ( not api controller ) implementation. I am wanting to use an ioc container to build and shre the session per request but am just concerned about the above issues. An example would be great but even a simple explanation would work too.

Sorry for some bragging but wanted to share this, quite illuminating post:
https://www.brentozar.com/archive/2017/05/case-entity-framework-cores-odd-sql/
discusses a case of really crazy (lets be straight - stupid) SQL that EF generates to insert 2 records with identity column in a batch. The author tries to rationalize/justify it, but I am not convinced. It's garbage, no way to justify it.
Compare it to what VITA generates in similar case - inserting hundreds of records in one go, with returning generating IDs to client, and automatically propagating values thru FK relationships for parent/child records - right in SQL script

Found this great ORM recently, was playing it with Sakila Database (https://www.jooq.org/sakila) in PostgreSQL 10. In my test,

var test = from ent in session.EntitySet<IActor>()
            where ent.FilmActors.Any(x => x.Film.Title.StartsWith("AGENT"))
            select ent;
var test_result = test.ToList();
var test_cmd = session.GetLastCommand();

Produce the following query statement:

select ent$."actor_id",
       ent$."first_name",
       ent$."last_name",
       ent$."last_update"
from "sakila"."actor" ent$
where ((select count(*)
        from "sakila"."film_actor" x$
          inner join "sakila"."film" t0$ on t0$."film_id" = x$."film_id"
        where (x$."actor_id" = ent$."actor_id")
        and   (t0$."title" like 'AGENT%' ESCAPE '\')) > 0)

I was expecting something like this which use EXISTS condition:

select ent$."actor_id",
       ent$."first_name",
       ent$."last_name",
       ent$."last_update"
from "sakila"."actor" ent$
where (exists (select null
        from "sakila"."film_actor" x$
          inner join "sakila"."film" t0$ on t0$."film_id" = x$."film_id"
        where (x$."actor_id" = ent$."actor_id")
        and   (t0$."title" like 'AGENT%' ESCAPE '\')))

There are many articles on "Exists Vs Count(*)", such as http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx

What's about Performance ?

https://www.exceptionnotfound.net/dapper-vs-entity-framework-vs-ado-net-performance-benchmarking/

Why Vita, wht not DApper ?

any good patterns and practices using Vita?

I use Enterprise Library 5.0 in ASP.NET 4.6.1

Suggestions: full source code useful, complete and sample using patterns & practices about it.

About it Vita, any step-by-step guide, or Getting Started – Quick Start that can be completed in 15-20 minutes?

I get you a good step-by-step guide sample like https://berniecook.wordpress.c...

I wanted to quickly cover off several assumptions before we get started:
install (requisites, tools required), configure, an running quickly.
Target will be an “go and ready” sample. Maybe better an real application sample

Mindly notes:

Any good getting started about it? step by step in 10 minutes?

IMHO, better samples for minimize learning curve are real applications with full source code and good patterns and practices

Full source code sample REAL application? not Demo, only real applications?

Like for small to medium applications development, but open source, if you
can sharing or maybe another real project or production-ready
application using main influences, are full of innovative ideas that can free our minds to explore new techniques, patterns and paradigms.

You want to use technologies that allow for rapid development, constant
iteration, maximal efficiency, speed, robustness and more. You want to
be lean and you want to be agile. You want to use technologies that will help you succeed in the short and long term. And those technologies are not always easy to pick out.

Thanks a lot for your big efforts.

Hi Rivantsov,

I like to seek ur advice. i have a scenario, lets say i have a function of "adding player" and "adding username".

function addPlayer() {
// open session
// add player detail
// save change
}

function addUsername() {
// open session
// add username detail
// save change
}

how will VITA ORM handle situation if i want to call "add username" in "add player", like scenario below?

function addPlayer() {
// open session
// add player detail
// save change

// add username if add player success
// if add username fail, rollback add player
addUsername();
}

Regards,
Michael

Hi,
First of all, thanks for the great framework!
I want to store all my Tenants information in one single database, but the transaction data will be kept in dedicated database per Tenant, is it doable with the framework? Can we have a single query from different database?
For now, I am actually creating 2 EntityApp, one for master database and one for transaction database, is it the right way to do?
Thanks in advance.
Jason Law

1. In EntityApp initialization, you need to call myApp.Init() explicitly before you call myApp.ConnectTo(dbSettings), otherwise exception is thrown with quite misleading message. It was not required in prev version.

(I will append issues to this list if they are found)
I will be fixing and pushing code asap

I can partialy use vita on my existing sqlite db, only the vdbtool does not works.
Partialy means that I can insert perfect new records through manually created entity interfaces, but reading those records throws invalid cast exception on every int (long) and DateTime field. My old existing program reads the vita-inserted records perfectly through System.Data.SQLite.
String and decimal properties are working fine.

ListCollectionView lcv = new ListCollectionView(session.GetEntities().OrderBy(x => x.CustName).ToList());
ICustomer e = session.NewEntity();
lcv.AddNewItem(e);

An unhandled exception of type 'System.Exception' occurred in Vita.dll
Additional information: Object {NoNewItem} is not Entity, cannot retrieve Record field.

Test project attached at codeplex. (Uploding here doesn't works...)

There are many use of database outside of VITA application, such as reporting which normally consists of handwritten query statement, the use of double quotes caused the table and column name become case sensitive, which make life difficult and time consuming.

The following article describe better the reasons of not using double quotes:
http://blog.lerner.co.il/quoting-postgresql/

nuget packages uploaded. Changes descriptions:
https://github.com/rivantsov/vita/wiki/code%20changes%20history
All (most) open issues should be resolved

'NpgsqlException' does not contain a definition for 'Code'

Hi Roman!

One customer reported problem with using using part of our software on his computer because it has only FIPS compliant cryptographic algorithms allowed on his system.

We found that part of our software uses not FIPS compliant cryptographic algorithms.
As a result of this we performed investigation to find all occurance non-compliant code. One we found in Vita.Data.Driver SqlSourceHasher class that uses MD5 hash algorithms. Documentation to method "Create" says that "TargetInvocationException" is thrown when the algorithm is used with Federal Information Processing Standards (FIPS) mode enabled, but is not FIPS compatible.

We are using VITA in the service part of our main software. ( The reported issue is not related to this software yet. We are working on it currently.)

I would like to ask you whether it is possible to replace MD hash algorithm with one FIPS compliant?

If I could, I would bring your attention to this type of algorithm:
"SHA256CryptoServiceProvider" that is (should be) FIPS compliant.

Best Regards

Milos Suchy

Hi.

I am trying to understand how the one to many attribute works.

I have an entity that links to another entity more than once on TWO foreign keys. If I just want to link one foriegn key I can just do the following:-

Child Entity

[EntityRef(FKParentId)]
IParentEntity Parent {get; set;}

Parent Entity

IList<IChildEntity> children {get; set;}

This works fine. But I want to link to the same parent on two seperate keys.

I have tried the following:-

Child Entity

[EntityRef(FKParentId1)]
IParentEntity Parent1 {get; set;}

[EntityRef(FKParentId2)]
IParentEntity Parent2 {get; set;}

Parent Entity

IList<IChildEntity> children1 {get; set;}
IList<IChildEntity> children2 {get; set;}

But this just throws a generic failed to build entity model error on Init() with no detail.

If I try and decorate the parent with a one to many attribute thus:-

[OneToMany("Parent1")]
IList<IChildEntity> children1 {get; set;}

[OneToMany("Parent2")]
IList<IChildEntity> children2 {get; set;}

I get a null reference exception on Init()

Even if I just have a single child reference e.g delete children2 I still get a null reference error.

Can anyone tell me where I'm going wrong?

P.S. I know having two foreign keys pointing to the same parent is fairly poor db / architectural design but I have no control over this and am unable to change the database schema at this point.

Regards,

Richard Lloyd.

Major changes (other than switch to .NET core) -
stored procedures are gone for good, now dynamic SQL only, including basic CRUD operations. SPs have good side - precompiled SQL code, compact and nice batch text - just lines of SP calls with parameters; but there's a downside - fixed set of columns, insert-update-select ALL. In many cases, it does not make sense to load entire entity and update-all while changing just one column. Plus, for SELECTs, with a general trend to GraphQL-like flexible API, when column set selected or updated can differ from call to call, it does not make much sense to go through pre-built stored procedures.
Switching to dynamic SQLs required some major changes in the way CRUD SQLs are generated. With batch mode, multiple SQL statements are packed together, in one DB command, with command parameters (one set for batch command) injected into SQLs where necessary across multipe CRUD SQLs. So what was needed is a flexible system of generating CRUD SQLs while injecting literals or parameters from 'global' list.
The other concern with SQL generation is multiple concatenations of SQL pieces into statements and then batches - causing a lot of string garbage generated, and pressure on memory and garbage collector. As a result, a new SQL templating system was built from scratch, to allow final merging of many-many string pieces into final batch at the end. That was quite an effort, the whole thing is a bit shaky, needs some cleanup in the future and clarity of basic concepts, but I think overall it is a good foundation for future development.
All SELECTs are now LINQ-based - CRUD SQL engine generates directly only insert/update/delete SQLs from templates; for SELECTS - it builds LINQ query (as experssion tree) and lets LINQ engine produce the SQL.
Big thing - packing multiple inserts into one INSERT statement. The batch engine sorts the updates (inserts,deletes) according to ref constraints (topological order), and then merges multiple inserts into the same table into 1 INSERT statement. Deletes are merged too, so multiple deletes from the same table are done with one DELETE statement - using 'where Id in (....)'. Run the unit tests, and have a look at SQL logs in bin folder - aren't some pieces simply beautiful?! Insertion of all secret questions for Login module - just one statement!
The LINQ engine - still old one, I could not complete the new one on time, so it is old one. But... the part with SQL generation and also integration with drivers (customization for providers) - this part is completely rebuilt with new SQL templating engine. That's a big improvement I think, compared to a mess it was before. I will get back to new LINQ engine soon, and hope to finish it and roll it into main code line.
There are multiple things that used to be there, but not ported yet:

• Query cache (saving generated SQLs)
• Entity (data) cache
• Include queries
• Authorization framework
• All web functionality (Web API integration)
• Out of many modules, only Login module is ported and tests. Logging module (for saving logs in db) - to be ported soon.

SQL Compact Edition is gone for good, SQLite is clearly the winner for in-process database.
Couple of unit tests are failing in console mode. One is SQLite file being locked although all connections have been closed (hopefully?) - any help in investigation would be appreciated.
Entity classes generation (classes behind interfaces, IL-generated) - handled by Proxemity package; bridge to Proxemity is Vita.Entities.Emit project/dll, included into main Vita package. The reasoning behind is on some platforms IL generation is not available at all (Apple), so there we can go with pre-compile generation classes as c# code - but this requires separation of IL-gen functionality from the main projects.
Again, sorry for the long wait, as usual - under-estimate how long things will take, should be better going forward, will deliver regular updates and improvements. Download, try, enjoy, feedback is welcome!

I need to sync data from one database to another, is there a way to copy a object from session1 to session2 ?

Regards.

Let me know if you find any issues

Please leave here your feedback about the project repo and other general issues

Do you have any plans to create database triggers directly in code or linq?

Regards.

Hi there just wondering if you have any plans on making Vita compatible with .Net Core 2.0 for more universal use?

Most likely not an issue but struggling to get this to work.

I need to get a record(s) based on a string value however whenever I try to request data when comparing two strings I get the String Comparison error.

I have tried

Dim importJobs = certificateSession.EntitySet(Of IImportJob).Where(Function(s) s.File = data.BatchCode.ToString())

Dim batchCodeString as string = data.BatchCode.Tostring()
Dim importJobs = certificateSession.EntitySet(Of IImportJob).Where(Function(s) s.File = batchCodeString)

Exception

{"Linq to SQL translation failed: Function CompareString not supported in queries
Possibly facilities you are trying to use are not supported. 
Try to reformulate/simplify the query. Hint: do not use c# functions/methods inside query directly. "}	Vita.Data.Linq.Translation.LinqTranslationException

The following example use ORMBenchmarksTest from https://github.com/exceptionnotfound/ORMBenchmarksTest

When using query syntax,

var players = from x in session.EntitySet<IPlayer>()
              where x.Team.Id == teamID
              select x;

-- where clause was used, parameter name is @P0
SELECT "Id", "FirstName", "LastName", "DateOfBirth", "TeamId"
FROM "dbo"."Player"
WHERE ("TeamId" = @P0)

However, when using method syntax,

var players = session.GetEntities<IPlayer>().Where(x => x.Team.Id == teamID).ToList();

-- no where clause, all records are retrieve from table
SELECT "Id", "FirstName", "LastName", "DateOfBirth", "TeamId" 
FROM "dbo"."Player";

I suppose both query and method syntax should produce the same select statement.

I am trying to run the demo to see how your implementation of views works however I get the below exception.

"System.NullReferenceException: Object reference not set to an instance of an object.
at Vita.Data.MsSql.MsSqlDbSqlBuilder.BuildSelectAllPagedCommand2008(EntityCommand entCommand)"

After a bit of debugging it appears that the order is trying to use the primary key on the table (for a view) but the property is null.

Line 55 in MsSqlDbSqlBuilder.cs
if (string.IsNullOrEmpty(strOrderBy))
strOrderBy = "ORDER BY " + table.PrimaryKey.KeyColumns.GetSqlNameList();

Connecting to a MsSQL 2008 database and the issue was raised for the vBookSales view.

Hello,

I find this project very intriguing and have began using it with my team on a new financial platform. Do you intend to keep this going? We'll surely begin contributing to it as time and feature requirements permits. From what we have seen, we love it. So good job and thanks for what you've done so far.

-Ryan

Have a look:
https://github.com/rivantsov/proxemity

The IL-emit functionality is in a separate library now. .NET Standard 2.0

Changes list with comments:
Code changes history

details will follow shortly

Getting the following error when attempting to run the basic tests:

Vita.dll!Vita.Entities.Runtime.EntitySession.ExecuteSelect() Line 410
{"Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified."}

Tried using Nuget to install "Microsoft.SqlServer.Types", which added a "SqlServerTypes" folder to the 100.Vita project and gave instructions for manually loading native assemblies. But it seems more geared to EF.

Not a bug but need some advice as to when to use EntitySet, GetEntities and ExecuteSearch.

I had an issue earlier where I was using GetEntities to get my records and using .Where to filter them. I later found that GetEntities was not converting the Where to SQL but EntitySet does. To get around this I decided to use the ExecuteSearch as I had the requirement to filter by more than one property so it made more sense to do it this way.

If I only wanted to get one record from table e.g. tblConfiguration which approach would you use and would it be cached?

session.EntitySet(of IConfiguration).FirstOrDefault(function(c) c.ConfigKey = key)
session.GetEntities(of IConfiguration).FirstOrDefault(function(c) c.ConfigKey = key)
session.ExecuteSearch(of SearchResults(of IConfiguration))(where,params)

is this project still alive?
so sad that so long time there are no activity.

if possible please upgrade to use all latest nuget lates dependencies.
especially npgsql

I have successfully used Vita converting data from some ugly old databases. Sometimes I had to do workarounds, but Vita was a great help. I have two questions about naming the entity properties.

1. Vita removes the underscores from table and field names. Can I switch it off?
2. A table named B_DESEMENY has two "master" tables B_DJOGVISZ and B_DBESOROL Vita declares the following properties:
   IBDJOGVISZ DE { get; set; }
   IBDBESOROL DERef { get; set; }
   From where come the names DE and DERef? Is there a way to give them a better name?

Thanks!

The readme for the Vita.Data.Postgres NuGet says that it explicitly targets Npgsql version 2.1.3 because of performance issues with some newer versions. However try to run with the NuGet dll, I get a run-time "File Not Found" exception looking for Npgsql 2.2.5

I also inherited a MSSQL db :(
It's full of colums like:
DAADOSZ char(11) not null default ' ',
vdbtool dbfirst declares:
[Column("DAADOSZ", DbType = DbType.AnsiStringFixedLength, Size = 11)]
string DAADOSZ { get; set; }

After session.NewEntity session.SaveChanges fails if DAADOSZ null, or string.Empty, or " " (space).
How can I write theese empty columns? (without real data)

UPDATE: with NullableAttribute
[Column("DAADOSZ", DbType = DbType.AnsiStringFixedLength, Size = 11), Nullable]
string DAADOSZ { get; set; }
I can write "" (empty string) to it. I can live with it:)

Hi Roman,

I am trying to store really huge object in the database.

At the beginning, it seems that everything works properly.

I can see in Sql Server profiler executing batches, but suddenly somewhere at the end of execution all stops. Program hangs, Task Manager shows that CPU utilisation is about 20 % for saving process. The mouse sometimes stops responding...

I am waited about half of hour without any result.

When I tried to pause code execution I got into SaveChangesInBatchMode procedure ,
concrete this for loop:

foreach (var rec in updateSet.AllRecords) {
rec.CustomTag = null; //clear temp ref that batch process has set
rec.SubmitCount++;
rec.EntityInfo.SaveEvents.OnSubmittedChanges(rec);
}
I do not know what this loop do and why it is so slow. Maybe the event handler SubmittedChanges can do this.

Maybe, some statistics from debugger can help you to estimate the size of the data.:
updateSet.AllRecords.Count=1774815
updateSet.BatchCommands.Count=1512
updateSet.EntityInfos.Count=26
UseTrensactions=True
UseOutPrams=True

From my point of view this operation takes longer than submiting changes to database.

I believe everything is executing in memory so there should be no such slowdown. That's just my guess.

Please, could you like to look at this problem and advise me how to solve it? Unfortunately, our objects are large and can not be divided into smaller parts.

Best Regards

Milos

http://www.codeproject.com/Articles/1117051/Using-locks-to-manage-concurrent-access-to-compoun

Is there any examples of using splim controllers to post json objects?

Regards,

I am new to mobile app development.
I want to use this great framework in my mobile app project based on xamarin.
My question is, since we don't keep session in server, are we always create/instantiated the EntityApp for each REST service call?

I have updated to 1.9.0 and my application does not work but i does not give any error, it just hangs, whas there any breaking changes to this version?

My friend Dave Clemmer just updated his article about VITA on codeproject:
https://www.codeproject.com/Articles/879568/VITA-A-Powerful-and-Flexible-ORM-and-Additional-Bu
lot of new stuff, updated examples, etc. Great overview of framework features!

Currently using the latest version of VITA for MsSQL 2008 but get the following error

"'IIF' is not a recognized built-in function name."

IIF appears to be available from 2012 onwards (https://msdn.microsoft.com/en-gb/library/hh213574.aspx)

MSSql, table with identity primary key.
If I'm calling session.NewEntity in a loop, and session.SaveChanges() after the loop, the identity values will not follow the entity creation order. If I want preserve the entity creation order in the identity, I have to call SaveChanges inside the loop.

New installation on VS2015 running SQL Server 2014. All tests passed except one. It looks like both SetupHelper.Driver and SetupHelper.ConnectionString are null, and that SetupHelper.Reset is never being called.

Test Name: TestGetSchema
Test FullName: Vita.UnitTests.Basic.GetSchemaTests.TestGetSchema
Test Source: C:\Projects\vita-master\5.UnitTests\Vita.UnitTests.Basic\GetSchemaTests.cs : line 18
Test Outcome: Failed
Test Duration: 0:00:56.6168251

Result StackTrace:

at Vita.Data.Model.DbModelConfig..ctor(DbDriver driver, DbOptions options, DbNamingPolicy namingPolicy, IDictionary`2 schemaMappings) in C:\Projects\vita-master\1.Framework\Vita.Core\Data\Model\DbModelConfig.cs:line 34
at Vita.Data.DbSettings..ctor(DbDriver driver, DbOptions options, String connectionString, String schemaManagementConnectionString, DbUpgradeMode upgradeMode, DbUpgradeOptions upgradeOptions, IDbInfoService dbInfoProvider, String dataSourceName, DbNamingPolicy namingPolicy) in C:\Projects\vita-master\1.Framework\Vita.Core\PublicAPI\DbSettings.cs:line 42
at Vita.UnitTests.Basic.GetSchemaTests.TestGetSchema() in C:\Projects\vita-master\5.UnitTests\Vita.UnitTests.Basic\GetSchemaTests.cs:line 23
Result Message:
Test method Vita.UnitTests.Basic.GetSchemaTests.TestGetSchema threw exception:
System.NullReferenceException: Object reference not set to an instance of an object.

Hi rivantsov,

How are you? Hope u doing well. :)

I have question regarding how to use VITA for full outer join?
Example: https://www.w3schools.com/sql/sql_join_full.asp

Thanks.
Regards,
Michael

The statement
Session.EntitySet(LockOptions.NoLock)
.Where(x => x.ERBKOD == "GBMB" && x.ERDBAZON > 0)
.Select(x => new { Value = x.ERFT, Kth = x.ER.DBSZE })
translates to
exec sp_executesql N'SELECT x$."ERFT" AS "Value", t0$."DBSZE" AS "Kth"
FROM "dbo"."B_EREDMENY" x$ WITH(NOLOCK)
INNER JOIN "dbo"."B_DBESOROL" t0$ ON t0$."DBAZON" = x$."ERDBAZON"
WHERE ((x$."ERBKOD" = @p0) AND (x$."ERDBAZON" > 0))',N'@p0 nvarchar(4)',@p0=N'GBMB'
in MSSQL.
It would be nice if the NOLOCK would apply to the joined table(s) too.

Hi,
I found this project while researching GraphQL. Needed a parser and I've used Irony. I didn't know about VITA until today. VITA looks like it could be the basis of a GraphQL server. I've frankly not found any good GraphQL servers for SQL Server written in .net. Just an idea I wanted to share and get feedback on.