How to Programming with Ormlite-servicestack

c#,orm,servicestack,ormlite-servicestack

db.Save() only INSERT or UPDATE entities i.e. it doesn't DELETE them. To delete, retrieve the entities or entity Ids you want to delete and use OrmLite's db.Delete* API's explicitly, e.g. something like: var removeUsersAttendingIds = listingEvent.UsersAttending .Where(u => u.UserAccountId == user.Id) .Select(u => u.Id); db.DeleteByIds<UserAccountListingEvent>(removeUsersAttendingIds); ...

ORMLIte[ServiceStack] . SaveReference method does not add items in the List

You need to explicitly commit your ADO.NET transaction, i.e: using(var trans = Db.OpenTransaction(IsolationLevel.ReadCommitted)) { Db.Save(user); Db.SaveReferences(user,user.HomeAddress); Db.SaveReferences(user,user.Orders); trans.Commit(); return Find(user.Id); } ...

Access ServiceStack session from ConnectionFilter

servicestack,castle-windsor,ormlite-servicestack

The earlier multi tenant ServiceStack example shows how you can use the Request Context to store per-request items, e.g. you can populate the Request Context from a Global Request filter: GlobalRequestFilters.Add((req, res, dto) => { var session = req.GetSession(); if (session != null) RequestContext.Instance.Items.Add( "UserName", session.UserName); }); And access it...

How do I register IDbConnectionFactory using Castle Windsor

This will be the equivalent in Castle Windsor: container.Register(Component.For<IDbConnectionFactory>() .Instance( new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider))); ...

ServiceStack OrmLite LeftJoin Issue

sql,servicestack,left-join,ormlite-servicestack

You should be able to use the new support for JOIN's in OrmLite's Typed SqlExpressions. It's best to use the latest v4.0.23 release on MyGet which includes improved support for selecting multiple columns across joined tables. With the new JOIN API's you can do what you need with something like:...

ServiceStack Ormlite transaction between services

sql-server,transactions,servicestack,ormlite-servicestack

You shouldn't have nested transactions, rather than calling across services to perform DB operations you should extract shared logic out either using a separate shared Repository or re-usable extension methods: public static class DbExtensions { public static void SaveCaseModel(this IDbConnection db, CaseModel case) { db.Insert<Case>(case); db.SaveAllReferences<CaseModel>(case); } } Then your...

Simple SELECT FOREIGN KEY with ServiceStack V3.9

c#,sql,servicestack,ormlite-servicestack

The easiest way to get the new Id would be to have the insert return it by using selectIdentity. You can do something like this: var newId = Db.Insert(DeviceInfo, selectIdentity: true); newId would then contain the AutoIncremented Id that was generated. Another thing that draws my eye is that the...

Error using Merge in Servicestack.OrmLite Sql Server

sql-server,transactions,ormlite-servicestack

This is working as intended, the error message indicates that it couldn't find a static relationship that could be merged which negates the purpose of the Merge command - to merge related result sets. When there is no statically defined relationship that exists, this is clearly an error the developer...

ServiceStack Ormlite transactions broken?

To use Transactions in OrmLite you should use the OpenTransaction() API, e.g: using (var trans = db.OpenTransaction()) { //... } I've added a couple new API's to be able to use an OrmLite transaction with a raw ADO.NET IDbCommand in this commit. Use a managed OrmLite DB Command Use a...

How to extend ServiceStack IDbConnectionFactory and MiniProfiler

servicestack,ormlite-servicestack,miniprofiler

To enable SQL Profiling in MiniProfiler you need to register the OrmLiteConnectionFactory to use MiniProfilers ProfiledDbConnection, e.g: Container.Register<IDbConnectionFactory>(c => new OrmLiteConnectionFactory(connectionString, SqlServerDialect.Provider) { ConnectionFilter = x => new ProfiledDbConnection(x, Profiler.Current) }); ...

How to use InsertOnly method in OrmLite?

servicestack,ormlite,ormlite-servicestack

You can find some examples in ApiSqlServerTests, e.g: db.InsertOnly(new Poco { FirstName = "Amy", Age = 27 }, q => q.Insert(p => new { p.FirstName, p.Age })); and async versions in ApiSqlServerTestsAsync, e.g: await db.InsertOnlyAsync(new Poco { FirstName = "Amy", Age = 27 }, q => q.Insert(p => new {...

Can I have OrmLite use lowercase for PostgreSQL column names rather than the provided lowercase with underbar naming?

postgresql,ormlite-servicestack

You can use specify your own naming strategy with: public class LowercaseNamingStrategy : OrmLiteNamingStrategyBase { public override string GetTableName(string name) { return name.ToLower(); } public override string GetColumnName(string name) { return name.ToLower(); } } OrmLiteConfig.DialectProvider.NamingStrategy = new LowercaseNamingStrategy(); ...

Owin with custom ORM framework (not Entity Framework)

c#,asp.net,entity-framework,ormlite-servicestack

The OrmLiteConnectionFactory should be registered as a singleton. You can then use it to create ADO.NET IDbConnection with: using (var db = dbFactory.OpenDbConnection()) { // } Once it's registered as a Singleton you could use a lazy property pattern that's like in the RepositoryBase class to simplify data access, e.g:...

Querying POCO's with References

c#,.net,servicestack,ormlite,ormlite-servicestack

This is called Self References in OrmLite and works with your above example: public class Order : IHasId<long> { [AutoIncrement] public long Id { get; set; } [References(typeof(Material))] public long MaterialId { get; set; } [Reference] public Material Material { get; set; } } public class Material : IHasId<long> {...

Does ServiceStack.OrmLite load Views from Sql Server?

c#,sqlite,servicestack,ormlite-servicestack

ServiceStack.OrmLite translates an object represented query into a SQL query. So, if you can call your view from a SQL string (which is the case) then you can do it with ServiceStack.OrmLite

OrmLite: SQLiteExceptionSQL logic error or missing database near “)”: syntax error

This issue is because you Foo doesn't have any columns to INSERT since Id is a autoincrementing primary key and Bar is a [Reference] property so no columns are saved so the INSERT SQL ends up looking like: INSERT INTO "Foo" () VALUES (); This would work if you Foo...

Using ServiceStack.OrmLite how can I add a bool column with a default value?

c#,ormlite-servicestack

On ServiceStack 4.0.36 with Azure SQL server I was able to use: [Default(typeof(bool), "0")] For the "false" default value on a boolean (bit) field....

Joining the same table multiple times in ServiceStack AutoQuery

c#,sql-server,servicestack,ormlite-servicestack

OrmLite (and by extension AutoQuery) doesn't support custom Aliases for Table JOIN's so you wont be able to query individual tables via AutoQuery. Although the normal use-case of: [Route("/purchase/query")] public class QueryPurchase : QueryBase<Purchase> { public int Id { get; set; } } client.Get(new QueryPurchase { Id = 1 }).PrintDump();...

ServiceStack.OrmLite SqlServer new Merge method doesn't work for different names of references

This should now be resolved with this commit which is available from v4.0.41+ that's now available on MyGet.

saving reference using ServiceStack ORMLite

orm,servicestack,ormlite-servicestack

You still need to provide the foreign key that OrmLite can use to store the relationship, e.g. either on the Child/ForeignKey table: public class Order { [AutoIncrement] public int Id { get; set; } [Reference] public Item Item { get; set; } public string ProUserId { get; set; } public...

Potential illegal fragment detected when using OrmLite SqlExpression with Mysql?

mysql,servicestack,ormlite-servicestack

You should normally use the Custom Sql API's like db.SqlList<T> when executing Custom SQL. If you want to use Custom SQL in a typed SqlExpression you should use the Unsafe* API's to by-pass any Custom SQL detection. But for the Select API of an SqlExpression you should only be adding...

ServiceStack.OrmLite get multiple result sets from a stored procedure

orm,many-to-many,servicestack,ormlite-servicestack

Unfortunately, ServiceStack.OrmLite does not support multiple result sets unless combined with Dapper. ServiceStack MARS (Multiple Active Result Sets) using ORMLite and Output Parameters Alternatively, you can use the .Net SqlCommand. Return multiple recordsets from stored proc in C# ServiceStack.OrmLite V4 notes: https://github.com/ServiceStack/ServiceStack.OrmLite ServiceStack.OrmLite V3 notes: https://github.com/ServiceStack/ServiceStack.OrmLite/tree/v3...

How does ServiceStack ORMLite handle Many to many relationships?

There is no explicit support for Many to Many relationships in OrmLite, i.e. you would handle it by creating a Poco that maps to each RDBMS table and just save each as you would normally, see this previous answer for an example.

How to debug ServiceStack Ormlite when things go wrong?

View Last SQL Executed The easiest approach is to just print out the last SQL Statement that was executed, i.e: db.GetLastSql().Print(); This works well for most OrmLite API's which only execute a single SQL statement. But it wont show all SQL executed with OrmLite's higher-level API's like db.Save() which can...

Whats the difference between ServiceStack.OrmLite packages and .Signed version?

c#,sql,servicestack,ormlite-servicestack

ServiceStack NuGet packages with a .Signed Suffix are strong-named, where other ServiceStack packages are not Strong named by default except for ServiceStack.Interfaces which is both Strong-Named and a pure PCL library for maximum accessibility. The ServiceStack download page maintains the list of available Signed packages....

Servicestack Ormlite seems to be ignoring my Database Schemas c#

This issue is resolved from the v4.0.39+ release of ServiceStack that's now available on MyGet.

One-to-Many relationship with ORMLite

sql,json,ormlite,ormlite-servicestack

An alternate more succinct example: public void Put(CreatePatient request) { var patient = new Patient { Name = request.Name, Insurances = request.Insurances.Map(x => new Insurance { Policy = i.Policy, Level = i.Level }) }; db.Save<Patient>(patient, references:true); } ...

ServiceStack taking a long time to execute stored procedure

c#,sql,servicestack,ormlite-servicestack,sql-server-2014

Adding an additional answer because really there were two issues. The real root seems to be with the stored procedure. I don't know why it wasn't consistently causing problems, but I rebuilt it using dynamic SQL to only include WHERE filters for parameters that are present. Originally, I had something...

ServiceStack Ormlite class with temporary field

c#,servicestack,data-annotations,ormlite-servicestack

Absolutely. You can simply add an [Ignore] attribute on the properties that are not part of your schema. From the documentation: Ignoring DTO Properties You may use the [Ignore] attribute to denote DTO properties that are not fields in the table. This will force the SQL generation to ignore that...

How to retrieve Data Annotation Alias(“tablename”) and Alias(“field name”) from ServiceStack ORMLite?

You can query this from OrmLite's ModelDefinition that's created for every POCO Table, e.g: var modelDef = typeof(Account).GetModelMetadata(); var tableName = modelDef.ModelName; var idName = modelDef.PrimaryKey.FieldName; Which in both cases will return the [Alias] if it exists....

How do I escape special characters when using ServiceStack OrmLite with SQLite?

c#,sqlite,servicestack,ormlite-servicestack

I've just added implicit support for escaping wildcards in this commit which will now escape wildcards in the typed expressions that make use of LIKE, namely StartsWith, EndsWith and Contains, e.g: using (var db = OpenDbConnection()) { db.DropAndCreateTable<Poco>(); db.Insert(new Poco { Name = "a" }); db.Insert(new Poco { Name =...

ServiceStack ormlite json does not deserialize from database

c#,serialization,servicestack,ormlite-servicestack,servicestack-text

The problem is that your class Inventory has a dictionary keyed by a complex class: public Dictionary<Item, int> Items { get; set; } However, according to the ServiceStack.Text documentation Any IDictionary is serialized into a standard JSON object, i.e: {"A":1,"B":2,"C":3,"D":4} Unfortunately your Item class cannot be represented as a simple...

ServiceStack's Ormlite Delete not working

The way db.Delete() API works has been updated so that NULL fields are moved out of the parameterized queries and appended to the SQL filter so this should now work from v4.0.37+ that's now available on MyGet. You can also delete rows in OrmLite by PrimaryKey with: Db.DeleteById<TestTable>(entity.Id); For generic...

ServiceStack “Declaration referenced in a method implementation cannot be a final method”

Your error message sounds like you're mixing different versions of ServiceStack together. Try clearing your NuGet packages Cache. Alternatively you can try the most recent v4.0.34 packages on MyGet. As for non ASP.NET MVC Examples, most of the ServiceStack Live Demos are Single Page Apps that don't use ASP.NET MVC...

Ormlite int based enums coming as varchar(max)

Add a [Flags] attribute to enums you want ServiceStack to treat as integers.

ServiceStack ORMLite - Can't Infer Relationship (because of DTO name?)

c#,.net,ormlite-servicestack

I've just tried creating an AutoQuery Service with all the types you've provided under a custom MyNamespace and it's working as expected (in the latest of ServiceStack), i.e: Service definition: namespace MyNamespace { [Route("/visits/{VisitId}/services", Verbs = "GET")] public class ServicesAtVisit : QueryBase<VisitService, ServiceAtVisit>, IJoin<VisitService, Service> { public int VisitId {...

Is there a built-in way for retrieving the parent object?

You should have a property like: [Reference] public ParentClass Parent { get; set; } on the ChildClass, and then you need to make sure that the property is properly filled up. You have the ParentId property on the child table in the database, so this shouldn't be any problem to...

ServiceStack Ormlite Join Wrapper

c#-4.0,generics,ormlite-servicestack

EDIT - Now seeing the use case, the error is related to casting to the base type and the builder storing more than one column selector for the concrete BaseEntity. Consider adding an abstract JoinType class, and modifying the JoinType class so it will apply the join for the builder....

Generating Class Object from Postgresql Database ServiceStack.Ormlite

c#,asp.net,postgresql,servicestack,ormlite-servicestack

OrmLite's primarily a code-first ORM but does have T4 Templates to help with initially generating the code-first POCO's for existing database tables. Although after generating the POCO's, I'd be maintaining them (i.e. code-first) from that point forward....

Can I generate SQL scripts with ServiceStack OrmLite?

c#,orm,ormlite-servicestack

This is trivial now that OrmLite extension methods are now mockable by providing your own OrmLiteResultsFilter. E.g. this ResultsFilter below records every sql statement executed and inherts the behavior from OrmLiteResultsFilter to return empty results: public class CaptureSqlFilter : OrmLiteResultsFilter { public CaptureSqlFilter() { SqlFilter = CaptureSql; SqlStatements = new...

ServiceStack ORMLIte : Id is necessary

The are several API's in OrmLite which rely on a primary key which will be either: A property annotated with the [PrimaryKey] attribute A property named Id Otherwise the first property is considered to be the primary key Some of OrmLite API's that rely on a primary key is: db.Update(entity)...

Post-registration action in ServiceStack

.net,authentication,servicestack,ormlite-servicestack

Customizing Authenticate Service Response I would recommend that you always return a concrete class in ServiceStack Services (i.e. instead of an anonymous object) this helps with being able to generate metadata services for the DTO types and lets you deserialize the response in typed APIs. You should also consider adhering...

Both calling Stored Procedure and SQL expression having problems when mapped to POCO which is not a domain object

c#,sql-server,orm,servicestack,ormlite-servicestack

From docs for using for calling Stored Procedures using SqlList, try instead calling your Stored Procedure with: var results = db.SqlList<DomainUserDto>( "EXEC TestJoinSupportForORMLite @UserId", new { UserId = id }); The issue is because you're returning ProUser's string Id but your DomainUserDto is trying to coerce it into an int...

How to do a GroupBy statement with ServiceStack OrmLite

You can also use a SqlExpression, e.g: var rows = db.SqlList<ShipperTypeCount>( db.From<Shipper>() .GroupBy(x => x.ShipperTypeId) .OrderBy("Total") .Select(x => new { x.ShipperTypeId, Total = Sql.As(Sql.Count("*"), "Total") })); Alternatively instead of using a concrete POCO you can use a generic dictionary to populate a dictionary of ShipperTypeId => Total, e.g: var q...

Load all hierarchical references with Servicestack ORMLite

c#,sql,servicestack,ormlite-servicestack

OrmLite's db.Load* API's is limited to loading 1-level depth of references. The Db.LoadReferences(instance) can be used to further fetch the disconnected POCO's references. You should also be mindful if loading references individually to avoid N+1 queries by loading them in a loop, i.e. when possible it's better to use a...

Service Stack ormlite generate http response for tables linked

First make sure your table has an auto incremented primary key. Second, Be careful with multiple inserts. You should call GetLastInsertedId directly after each insert. Under the hood in SQL it translates into SELECT SCOPE_IDENTITY() Your code should look more like the following: Db.Insert(new DeviceInfo.DeviceInfo { DeviceType = NewDevice.Type, HumanReadDevId...

Convert SQL to ServiceStack.ORMLite Sql Server

c#,sql-server,tsql,servicestack,ormlite-servicestack

For custom SQL like this, you'd use OrmLite's Custom SQL API's, with something like: var results = db.Select<Poco>(@"select convert(date, t.TransactionDate) [Date], tm.TeamName, a.AccountName, count(distinct(t.RequisitionNumber)) Total from task.tblTransactions t inner join task.tblRequisitions r on r.RequisitionNumber = t.RequisitionNumber inner join task.tblAccounts a on a.AccountNumber = r.AccountNumber inner join Team tm on tm.DivisionId...

ServiceStack.OrmLite support for IBM DB2

db2,servicestack,ormlite-servicestack

No there isn't a provider for DB2, the 8 OrmLite Dialect Providers that are available are listed on the OrmLite Home Page

MySQL decimal column precision with Servicestack ORMLite

mysql,servicestack,ormlite-servicestack

I've just added support for custom decimal precision for MySql in this commit. This change is available from v4.0.37+ that's now available on MyGet....

How to globally change the command timeout using ServiceStack.OrmLite