Posts Tagged ‘MS SQL Server’
There’s a lot of content for latest CTP, CTP4, for Entity Framework “new features”. It’s mainly focused on Code First stuff, that fills the triad with Database First and Model First. I like Code First but what I also like is maintainable code. Hence I was more trying not to use convention-over-configuration, in opinion good for only small projects, and focus on specifying everything the way I want it to be, especially with my database structure (yes, I’m data consistency and storage freak).
Let’s model some kind of simple library and try to use there couple of Entity Framework’s features. And to make things worse, try to do it with ADO.NET provider for Firebird.
Good news is I succeeded. My database structure was my first and non-touchable object (to be honest the first was the idea what to create and how to represent it in database) together with the idea of objects.
Here’s the script for Firebird database (Did you noticed we’re fully Entity Framework 4 compatible?). See the quoted column names? Yes, not nice, but later in mapping in C# it looks nicer. 
And I mixed the mapping with one for MS SQL Server (see below).
RECREATE TABLE Authors (
"id" INT NOT NULL,
"FirstName" VARCHAR(255) NOT NULL,
"LastName" VARCHAR(255) NOT NULL,
CONSTRAINT PK_Authors PRIMARY KEY ("id")
);
RECREATE TABLE Books (
"id" INT NOT NULL,
"Discriminator" char(2) NOT NULL,
"Title" VARCHAR(1000) NOT NULL,
"Published" TIMESTAMP NOT NULL,
"ID_Author" INT NOT NULL,
"IssuesPerYear" SMALLINT,
"Price" DECIMAL(9,0),
CONSTRAINT PK_Books PRIMARY KEY ("id")
);
RECREATE TABLE Languages (
"id" INT NOT NULL,
"LanguageName" VARCHAR(100) NOT NULL,
"LanguageAbbrevation" CHAR(3),
CONSTRAINT PK_Languages PRIMARY KEY ("id")
);
RECREATE TABLE Translators (
"id" INT NOT NULL,
CONSTRAINT PK_Translators PRIMARY KEY ("id")
);
RECREATE TABLE Translators_Languages (
"ID_Language" INT NOT NULL,
"ID_Translator" INT NOT NULL,
CONSTRAINT PK_Translators_Languages PRIMARY KEY ("ID_Language", "ID_Translator")
);
ALTER TABLE Books ADD CONSTRAINT FK_Book_Author FOREIGN KEY ("ID_Author")
REFERENCES Authors("id")
ON DELETE CASCADE
;
ALTER TABLE Translators_Languages ADD CONSTRAINT FK_TL_Languages FOREIGN KEY ("ID_Language")
REFERENCES Languages("id")
ON DELETE NO ACTION
;
ALTER TABLE Translators_Languages ADD CONSTRAINT FK_TL_Translators FOREIGN KEY ("ID_Translator")
REFERENCES Translators("id")
ON DELETE NO ACTION
;
ALTER TABLE Translators ADD CONSTRAINT FK_TypeConstraint FOREIGN KEY ("id")
REFERENCES Authors("id")
ON DELETE NO ACTION
;
So it’s time to create mapping right? Nope. Now I’ll switch my brain from ER thinking into OO thinking mode. Here’s the world of entities (sure I made some adjustments to fit some Entity Framework features, like Complex Types):
public abstract class Book
{
public int ID { get; protected set; }
public string Title { get; set; }
public DateTime Published { get; set; }
public Author Author { get; set; }
public int AuthorID { get; set; }
}
public class RealBook : Book
{
public decimal Price { get; set; }
}
public class Magazine : Book
{
public short IssuesPerYear { get; set; }
}
public class Author
{
public int ID { get; protected set; }
public Name FullName { get; set; }
public ICollection<Book> Books { get; set; }
public Author()
{
this.Books = new List<Book>();
}
}
public class Translator : Author
{
public ICollection<Language> Languages { get; set; }
public Translator()
{
this.Languages = new List<Language>();
}
}
public class Language
{
public int ID { get; protected set; }
public string LanguageName { get; set; }
public string LanguageAbbrevation { get; set; }
}
#region Complex Types
public class Name
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
#endregion
As you see I’m using pure POCOs.
So far we haven’t touched any Entity Framework related stuff. So it’s time to create our context and all DAL related stuff. Mine is very simple and exposes only few properties and methods, just to keep it simple and focus on the aim.
public class LibraryContext : DbContext
{
public LibraryContext(DbConnection connection)
: base(connection)
{
this.ObjectContext.ContextOptions.LazyLoadingEnabled = false;
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Configurations.Add(new BookConfiguration());
modelBuilder.Configurations.Add(new AuthorConfiguration());
modelBuilder.Configurations.Add(new TranslatorConfiguration());
modelBuilder.Configurations.Add(new LanguageConfiguration());
modelBuilder.Configurations.Add(new NameConfiguration());
}
public string CreateDatabaseScript()
{
return this.ObjectContext.CreateDatabaseScript();
}
public IDbSet<Book> Books
{
get { return this.Set<Book>(); }
}
public IDbSet<Author> Authors
{
get { return this.Set<Author>(); }
}
}
public class DoNothingWithMyDatabase<TContext> : IDatabaseInitializer<TContext>
where TContext : DbContext
{
public void InitializeDatabase(TContext context)
{ }
}
Pretty simple, isn’t it. I’m using new stripped down objects DbContext (← ObjectContext) and IDbSet/DbSet (← IObjectSet/ObjectSet).
You may also notice, I’ve created object which implements IDatabaseInitializer. That’s because, by default, Entity Framework will try to create database for you and check whether your database matches model (CreateDatabaseOnlyIfNotExists). I‘m controlling my database. You have to add somewhere into your application before doing anything with the context call to SetInitializer:
Database.SetInitializer(new DoNothingWithMyDatabase<LibraryContext>());
And finally we’re ready to dive into the mapping. Again, you can type the mapping directly into overridden OnModelCreating, useful if you have only couple of diversions from default conventions. I created configuration classes where I specified as much as I want. And I need (almost) everything to be nailed down (remember, data consistency and storage freak ) as I don’t want to be surprised when something in database or in entities changes and application will start behave weird.
class BookConfiguration : EntityConfiguration<Book>
{
public BookConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.Property(x => x.Title).IsRequired().IsVariableLength().HasMaxLength(1000).IsUnicode();
this.Property(x => x.Published);
this.HasRequired(x => x.Author).WithMany(a => a.Books).WillCascadeOnDelete().HasConstraint((b, a) => b.AuthorID == a.ID);
this.MapHierarchy()
.Case<Book>(x => new
{
id = x.ID,
Title = x.Title,
Published = x.Published,
ID_Author = x.AuthorID,
})
.Case<RealBook>(x => new
{
Discriminator = "B",
Price = x.Price,
})
.Case<Magazine>(x => new
{
Discriminator = "M",
IssuesPerYear = x.IssuesPerYear,
})
#if FB
.ToTable("BOOKS");
#else
.ToTable("Books");
#endif
}
}
class AuthorConfiguration : EntityConfiguration<Author>
{
public AuthorConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.HasMany(x => x.Books);
this.MapHierarchy(x => new
{
id = x.ID,
FirstName = x.FullName.FirstName,
LastName = x.FullName.LastName,
})
#if FB
.ToTable("AUTHORS");
#else
.ToTable("Authors");
#endif
this.MapHierarchy().Case<Translator>(x => new
{
id = x.ID,
})
#if FB
.ToTable("TRANSLATORS");
#else
.ToTable("Translators");
#endif
}
}
class TranslatorConfiguration : EntityConfiguration<Translator>
{
public TranslatorConfiguration()
{
this.HasMany(x => x.Languages).WithMany()
#if FB
.Map("TRANSLATORS_LANGUAGES",
#else
.Map("Translators_Languages",
#endif
(t, l) => new
{
ID_Translator = t.ID,
ID_Language = l.ID,
});
}
}
class LanguageConfiguration : EntityConfiguration<Language>
{
public LanguageConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.Property(x => x.LanguageName).IsRequired().IsVariableLength().HasMaxLength(100).IsUnicode();
this.Property(x => x.LanguageAbbrevation).IsFixedLength().HasMaxLength(3).IsUnicode();
this.MapSingleType(x => new
{
id = x.ID,
LanguageName = x.LanguageName,
LanguageAbbrevation = x.LanguageAbbrevation
})
#if FB
.ToTable("LANGUAGES");
#else
.ToTable("Languages");
#endif
}
}
class NameConfiguration : ComplexTypeConfiguration<Name>
{
public NameConfiguration()
{
this.Property(x => x.FirstName).IsRequired().IsVariableLength().HasMaxLength(255).IsUnicode();
this.Property(x => x.LastName).IsRequired().IsVariableLength().HasMaxLength(255).IsUnicode();
}
}
I don’t know whether it’s worth to describe the lines. Should be understandable if you know how the entities and database look like. Just maybe small notice. Besides Complex Types we used two most common inheritance mapping scenarions – TPH aka Table Per Hierarchy for Books and TPT aka Table Per Type for Authors (there’s also TPC (Table Per Concrete Type)). However if you have questions feel free to use comments, if I’ll know answer I’ll be happy to reply.
And finally some really simple application to test the result:
If you define #define FB it’ll use Firebird database else MS SQL Server.
Database.SetInitializer(new DoNothingWithMyDatabase<LibraryContext>());
Action<LibraryContext> doSomething = (context) =>
{
Console.WriteLine(context.CreateDatabaseScript());
var query = context.Books
.Select(b => new
{
BookName = b.Title,
AuthorName = b.Author.FullName.LastName + ", " + b.Author.FullName.FirstName
})
.OrderBy(x => x.BookName);
Console.WriteLine((query as ObjectQuery).ToTraceString());
var data = query.ToArray();
foreach (var item in data)
{
Console.WriteLine("Book {0} written by {1}.", item.BookName, item.AuthorName);
}
};
#if FB
using (LibraryContext context = new LibraryContext(new FbConnection(@"database=localhost:ctp4;username=sysdba;password=masterkey;pooling=true;")))
{
doSomething(context);
}
#else
using (LibraryContext context = new LibraryContext(new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ctp4;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True")))
{
doSomething(context);
}
#endif
I’m glad I was able to figure out how to map everything without touching database structure (although it’s pretty straightforward) or letting the default rules to kick in. Hope it will help you if you struggle with something (and hope the naming will not change much in future). It’s also nice to see the whole infrastructure fits together and changing provider, in my case for Firebird’s, doesn’t make the code to blow out.
From time to time I have to run two or more queries that I know will always be two or more – like some first/skip records and also total count. If you write it as two queries and execute, that means two round trips to database. Although it may not matter if the network latency is very small, why not to challenge myself and try to find some workarounds.
Sure you can create some stored procedures and get the data back from these, but I was thinking about more LINQ to Entitiesish way. I recalled a way I one time used inside one project. Although it was done in pure SQL, it, as it turned out, works, kind of, for LINQ to Entities as well.
The idea is using “one row table” and put the queries as columns. Let me demonstrate:
select
(select foo, bar from table1 where ...),
(select baz, foo from table2 where ...)
from OneRowTable;
Where the OneRowTable can be specially created table or i.e. for Firebird RDB$DATABASE or for Oracle Database dual. It isn’t the nicest SQL (and also challenges optimizer), but works. In columns as queries you can put anything you want as long as it is syntactically correct.
OK, what about the Entity Framework or LINQ to Entities respectively. I created the “one row table” first:
create table OneRowTable(x bit primary key);
insert into OneRowTable values (0);
The table needs to have the primary key to be able to import it into entity model, the datatype doesn’t matter (I was using MS SQL, hence the bit).
What about the queries? Similar approach:
var allinone = context.OneRowTable.Select(_ => new
{
AData = context.a.Where(a => a.x.HasValue && a.x.Value > 10).Select(a => new { A1 = a.id, A2 = a.id * 2 }),
BData = context.b.Where(b => b.id < 999).Select(b => new { B1 = b.id, B2 = b.y }),
});
string query = (allinone as ObjectQuery).ToTraceString();
var data = allinone.First();
var adata = data.AData;
var bdata = data.BData;
The a and b are my testing tables. You can check there’s only one query executed. Encapsulating this into some method is only piece of cake.
And how the query looks like? Well for my MS SQL test:
SELECT
[UnionAll1].[x] AS [C1],
[UnionAll1].[C2] AS [C2],
[UnionAll1].[C1] AS [C3],
[UnionAll1].[id] AS [C4],
[UnionAll1].[id1] AS [C5],
[UnionAll1].[C3] AS [C6],
[UnionAll1].[C4] AS [C7],
[UnionAll1].[C5] AS [C8],
[UnionAll1].[C6] AS [C9]
FROM (SELECT
[Project1].[C2] AS [C1],
[Extent1].[x] AS [x],
1 AS [C2],
[Project1].[id] AS [id],
[Project1].[id] AS [id1],
[Project1].[C1] AS [C3],
CAST(NULL AS int) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS varchar(1)) AS [C6]
FROM [dbo].[OneRowTable] AS [Extent1]
LEFT OUTER JOIN (SELECT
[Extent2].[id] AS [id],
[Extent2].[id] * 2 AS [C1],
1 AS [C2]
FROM [dbo].[a] AS [Extent2]
WHERE ([Extent2].[x] IS NOT NULL) AND ([Extent2].[x] > 10) ) AS [Project1] ON 1 = 1
UNION ALL
SELECT
2 AS [C1],
[Extent3].[x] AS [x],
[Extent4].[id] AS [id],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS int) AS [C4],
[Extent4].[id] AS [id1],
[Extent4].[id] AS [id2],
[Extent4].[y] AS [y]
FROM [dbo].[OneRowTable] AS [Extent3]
CROSS JOIN [dbo].[b] AS [Extent4]
WHERE [Extent4].[id] < 999) AS [UnionAll1]
ORDER BY [UnionAll1].[x] ASC, [UnionAll1].[C1] ASC
Not exactly the original shape. The translator took another way creating two one row results and using union all to get it into one query. Except this, the query is in general the same (the explicit joins are as result same as the subselects, though little bit more confusing in this case).
Again, this isn’t general purpose way of doing it and may result in worse performance than running queries separately and I would recommend using it only after careful testing and on controlled limited set of queries.
Julie Lerman created a post about spatial data usage in Entity Framework. Her conclusions are correct. But because from last three projects I did on Entity Framework two of them are working with spatial data (every customer wants a Google Maps or Bing Maps on website, it’s like cup holders in cars) I would like share my solution as well some ideas behind why I used this approach.
First and foremost Entity Framework doesn’t support spatial data now. So you have to create some workaround. The idea getting the data through blob is exactly what I created. In my tables where I need to save some coordinates (it’s mainly a point) I create simple column with this data type – I consider this clean initial work better, because maybe sometimes/somewhere/somebody will work with this structures so to have it clean. Then for the workaround I add XXX_bin column where I’ll store the binary representation of spatial data and use it for Entity Framework. Something like this:
[Location] geography Default geography::STGeomFromText('POINT EMPTY', 4326) NOT NULL,
[Location_bin] Varbinary(max) NOT NULL,
Because I’m working only through Entity Framework I need to update the Location automatically as there might be (and are) indices or other stuff working with it. To make it transparent I’m using insert or update trigger:
if (update(Location_bin))
begin
update %tablename% set
Location = geography::STGeomFromWKB(Location_bin, 4326)
where
ID in (select ID from inserted);
end
If you expect somebody updating the Location column directly too, you need to create another trigger and make sure you’ll not end up in infinite loop.
To support the – in my case – points for UI developers I create in every entity computed property turning Location_bin into my LatLong struct. This struct is using Microsoft.SqlServer.Types namespace.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Types;
namespace FooBar
{
public struct LatLong
{
public double Lat { get; set; }
public double Long { get; set; }
public static LatLong FromSqlBytes(byte[] bytes)
{
SqlGeography g = SqlGeography.STGeomFromWKB(new SqlBytes(bytes), 4326);
return new LatLong { Lat = g.Lat.Value, Long = g.Long.Value };
}
public byte[] ToSqlBytes()
{
return SqlGeography.Point(this.Lat, this.Long, 4326).STAsBinary().Buffer;
}
}
}
public LatLong Location
{
get
{
return LatLong.FromSqlBytes(this.Location_bin);
}
set
{
this.Location_bin = value.ToSqlBytes();
}
}
This creates almost seamless support for other developers when using spatial data in Entity Framework – inserting, updating, deleting all works without notice from others (if you’re not looking into internal implementation).
There are only two problems, both solvable.
First one is that developer cannot filter/sort/… using Location property. This isn’t probably a big problem. As (s)he doesn’t have spatial methods (like i.e. Distance) available, so the queries will not make sense. Which brings me to the other problem and that’s the querying. With EFv1 I created stored procedures for these queries and returned data through these. Same works for EFv4. If you have strict set of results you need, this is probably easiest solution. The other one, working in EFv4 is LINQ function imports via EdmFunction attribute. For computations I’m going to use I create function in T-SQL doing the work and I import it into my solution thru above noted attribute. The simple Distance method may look like:
create function Distance
(
@Location_bin1 varbinary(max),
@Location_bin2 varbinary(max)
)
returns int
as
begin
return geography::STGeomFromWKB(@Location_bin1, 4326).STDistance(geography::STGeomFromWKB(@Location_bin2, 4326));
end
And
[EdmFunction("model.Store", "Distance")]
internal static int Distance(this byte[] location1, byte[] location2)
{
throw new NotSupportedException();
}
Sure, when calling this function for a big resultsets it may be a performance bottleneck (I recommend doing there as much work as possible, even at the price of more functions doing similar stuff, to not create a deep function calls.), but with additional filters it works well.
protected IQueryable<Something> SomethingInDistance(LatLong point, int distance)
{
byte[] spatialData = point.ToSqlBytes();
return this.Somethings.Where(o => o.Location_bin.Distance(spatialData) < distance);
}
Another way could be to create a view (if you know the parameters in advance) and do explicit join (table/entityset <> view/entityset) in EF. Or do the filtering on client (if reasonably small). And I’m sure I’ll find other ways how to get the data you want back, simply choose what fits your needs best.
Although it may look like a lot of work, it isn’t. In fact you’re pretty fast (there’s a high level of reusability) if you know what to do (and after one implementation you will).
Today I uncovered a magic command from Entity Framework v4 when you create M:N association and the underlying table is defined with both columns as identity and you insert there. I don’t what’s it good for, as this table in fact only stores the two IDs to connect other tables. But somebody may build some logic on identity there, sure.
When I first saw the command, I was completely stunned. I had no idea what’s going on there and whether I see there one or more commands. You can have fun too:
declare @generated_keys table([ID_A] int, [ID_B] int)
insert [dbo].[A_B]
output inserted.[ID_A], inserted.[ID_B] into @generated_keys
default values
select t.[ID_A], t.[ID_B]
from @generated_keys as g join [dbo].[A_B] as t on g.[ID_A] = t.[ID_A] and g.[ID_B] = t.[ID_B]
where @@ROWCOUNT > 0
As an old school guy I was first looking for semicolons and then later tried to decode it by “parsing” the content.
Isn’t it nice… 
I have a strong feeling that MS SQL knows I don’t like it. I don’t say it’s a bad database, but sometimes it’s really “interesting”.
OK, so what I faced this time? Let’s start with basic facts. The NULL value in database is a special value. NULL and NULL is again NULL, NULL and False is again NULL and NULL isn’t equal to NULL. Two days ago I was creating a chain of records in database (which isn’t too relationalish, but …) and I needed unique constraint on one column. No problem you may think. So did I. But not in MS SQL.
Check this example:
1> create table test(id int primary key, foo int);
2> create unique index idx_text on test(foo);
3> go
1> insert into test values (1, null);
2> go
(1 rows affected)
1> insert into test values (2, null);
2> go
Msg 2601, Level 14, State 1, Server X2-001SQLEXPRESS, Line 1 Cannot insert duplicate key row in object 'dbo.test' with unique index 'idx_text '.
The statement has been terminated.
What a mess! Looks like somebody in MS SQL engine team thinks NULL == NULL. After some railing, testing on Firebird and asking my friend about Oracle, I googled: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299229. Looks like I’m not alone; feeling better.
Luckily I’m working on MS SQL 2008 and I can use filtered indices. Created unique index with where clause where <column> is not null and I’m done. But boy that was a bitter finding. Hope I’ll not be punched to the face with these basics again in an at least two months.
I finally got my hands on the new feature in EF4, Code Only. It’s now available in feature CTP. What’s the code only? Very shortly you write just a code and express your mapping in code as well. There’s no model, no edmx file, no CSDL, MSL, SSDL files.
The current version is pretty limited, it’s more preview than something you can do some work with (which I wasn’t aware of and I was little disappointed). You can read more about what will be available on http://blogs.msdn.com/efdesign/archive/2009/06/10/code-only.aspx, http://blogs.msdn.com/efdesign/archive/2009/08/03/code-only-enhancements.aspx and http://thedatafarm.com/blog/data-access/next-version-of-ef-code-only-design-laid-out-by-ms/.
I mainly wanted to try it on a non standard (read: not “follow this demo”) MS SQL database and on Firebird as well. The first bad news is, that current CTP supports only SqlClient. And probably some following previews will too, as the other providers model will be built on top of it (but I’ll try to push this as much as I can, to see some Firebird demo soon). The other is, that the currently available version is limited, even features in first Code Only blog post are not working. 
Right now you can only create a context and use it for work with all defaults – default names for tables, default mapping etc.
using (SqlConnection conn = new SqlConnection(@"Data Source=.sqlexpress;Initial Catalog=testovaci;Integrated Security=True;Pooling=False"))
{
ContextBuilder<COContext> builder = new ContextBuilder<COContext>();
COContext context = builder.Create(conn);
string script = context.CreateDatabaseScript();
context.Masters.ToArray();
}
Nothing special. If you try it with FirebirdClient, you’ll get NotSupportedException immediately. Dammit.
using (FbConnection conn = new FbConnection("database=localhost:rrr.fdb;user=sysdba;password=sysdba"))
{
ContextBuilder<COContext> builder = new ContextBuilder<COContext>();
builder.Create(conn);
}
Despite the frustration I found couple of new interesting extension methods (in Microsoft.Data.Objects, also ContextBuilder sits there). One of these is CreateDatabaseScript. This method works not only with Code Only created ObjectContext, but on every ObjectContext. So you can create your database creation script during runtime even if you’re using i.e. EDMX file easily. I’m also seeing great opportunity for this with Code Only setup during runtime. You can have different builds with different setups and create script for users based on selection. And by the way, there’s also CreateDatabase method available. But again right now works only with MS SQL only. But I know other databases will be supported as well, I have tested some pieces already.
I like the Code Only feature, maybe more than Model First (probably because I’m using ERD tools to model my databases). And I’m looking forward to see more progress on it, and also the model for 3rd party providers.
Shortly: Very.
I hate the idea of instead of triggers and I made a couple of blog posts several times. And even worse is the implementation behavior on MS SQL. And the cascade constraints are bad too.
Let’s suppose this simple definition.
create table master(id int primary key, foo nvarchar(20));
create table detail(id int primary key, id_master int not null, bar nvarchar(20));
alter table detail add foreign key (id_master) references master(id) on delete cascade;
Nothing special. Works fine, no problems expected. Until you try to define instead of delete trigger on detail table. I.e.
create trigger tr_test on detail
instead of delete as
begin
select 1;
-- now the fun begins
end
You get an nice error: Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'tr_test' on table
'detail'. This is because the table has a FOREIGN KEY with cascading DELETE or
UPDATE.. What the hell??? 
Why?
Again I got a direct proof, that in this area the MS SQL is wrong, very wrong. I would be willing to accept the limitation when I’ll be doing there some master table manipulation (yes, as I said, cascade constraints are bad too). But this? I can hardly believe my eyes. The trigger is almost empty. Or am I missing someting on backround that limits this to work?
Another “feature” that makes me love Firebird more.
I found nice hack on MS SQL. On Firebird I’m using sometimes computed column(s) with select in definition. It can be performance (or concurrency) problem, but if you use it carefully it’s helpful. And I was missing this feature on MS SQL. But accidentally I found solution/workaround.
You can create a function, that will do the select and return the value you need. The only problem is, that you’re creating dependency between the column and the function.
You can create function for instance:
create function FooBar
(
@ID int
)
returns bit
as
begin
declare @result bit;
if /* some code with (sub)select */
begin
set @result = 1;
end
else
begin
set @result = 0;
end
return @result;
end
And use it in column for table (it’s just example ):
alter table Foo add Bar as (case when dbo.FooBar(ID) = 1 then 'foo' else 'bar' end);
Not nice as in Firebird where you can just use the select, but works. I haven’t measured any performance hit, but sure there will be some slowdown. But if you really need it …
I don’t like the model of instead of and after triggers in MS SQL. The before and after ones are, in my opinion, better. Anyway I needed to do some complex tests, not possible with check constraints, before inserting. Hence I jumped into instead of trigger. No problem in a view. Or not?
The Entity Framework is smart enough to get all server generated columns back when inserting (or updating) – see StoreGeneratedPattern. But the problem is how the identity column, often used for primary keys, is retrieved. The command issued after insert looks like (particular shape depends on other store generated columns and concurrency checks): select <PK column> from <some table> where @@ROWCOUNT > 0 and <PK column> = scope_identity().The problem is, that if you generate new PK value in trigger, it is different scope. Thus this command returns zero rows and EF will throw exception.
Hmm, this isn’t the way. So one of the solutions is move away from instead of triggers and use stored procedures (remember that you need all three) and result binding (you might be forced to create SPs (of fake ones in SSDL) for some related objects too). It’s not easiest solution, but works nice and you don’t have to introduce hacks etc.
