The SAP Sybase IQ .NET Data Provider supports Entity Framework 4.3, a separate package available from Microsoft. To use Entity Framework 4.3,you must add it to Visual Studio using Microsoft's NuGet Package Manager.
One of the new features of Entity Framework is Code First. It enables a different development workflow: defining data model objects by simply writing C# or VB.NET classes mapping to database objects without ever having to open a designer or define an XML mapping file. Optionally, additional configuration can be performed by using data annotations or the Fluent API. Models can be used to generate a database schema or map to an existing database.
Here's an example which creates new database objects using the model:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using iAnywhere.Data.SQLAnywhere;
namespace CodeFirstExample
{
[Table( "EdmCategories", Schema = "DBA" )]
public class Category
{
public string CategoryID { get; set; }
[MaxLength( 64 )]
public string Name { get; set; }
public virtual ICollection<Product> Products { get; set; }
}
[Table( "EdmProducts", Schema = "DBA" )]
public class Product
{
public int ProductId { get; set; }
[MaxLength( 64 )]
public string Name { get; set; }
public string CategoryID { get; set; }
public virtual Category Category { get; set; }
}
[Table( "EdmSuppliers", Schema = "DBA" )]
public class Supplier
{
[Key]
public string SupplierCode { get; set; }
[MaxLength( 64 )]
public string Name { get; set; }
}
public class Context : DbContext
{
public Context() : base() { }
public Context( string connStr ) : base( connStr ) { }
public DbSet<Category> Categories { get; set; }
public DbSet<Product> Products { get; set; }
public DbSet<Supplier> Suppliers { get; set; }
protected override void OnModelCreating( DbModelBuilder modelBuilder )
{
modelBuilder.Entity<Supplier>().Property( s => s.Name ).IsRequired();
}
}
class Program
{
static void Main( string[] args )
{
Database.DefaultConnectionFactory = new SAConnectionFactory();
Database.SetInitializer<Context>( new DropCreateDatabaseAlways<Context>() );
using ( var db = new Context( "DSN=Sybase IQ Demo" ) )
{
var query = db.Products.ToList();
}
}
}
}
To build and run this example, the following assembly references must be added:
EntityFramework iAnywhere.Data.SQLAnywhere.v4.0 System.ComponentModel.DataAnnotations System.Data.Entity
Here is another example that maps to an existing database:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using iAnywhere.Data.SQLAnywhere;
namespace CodeFirstExample
{
[Table( "Customers", Schema = "GROUPO" )]
public class Customer
{
[Key()]
public int ID { get; set; }
public string SurName { get; set; }
public string GivenName { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public string CompanyName { get; set; }
public virtual ICollection<Contact> Contacts { get; set; }
}
[Table( "Contacts", Schema = "GROUPO" )]
public class Contact
{
[Key()]
public int ID { get; set; }
public string SurName { get; set; }
public string GivenName { get; set; }
public string Title { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
[ForeignKey( "Customer" )]
public int CustomerID { get; set; }
public virtual Customer Customer { get; set; }
}
public class Context : DbContext
{
public Context() : base() { }
public Context( string connStr ) : base( connStr ) { }
public DbSet<Contact> Contacts { get; set; }
public DbSet<Customer> Customers { get; set; }
}
class Program
{
static void Main( string[] args )
{
Database.DefaultConnectionFactory = new SAConnectionFactory();
Database.SetInitializer<Context>( null );
using ( var db = new Context( "DSN=SAP Sybase IQ 16 Demo" ) )
{
foreach ( var customer in db.Customers.ToList() )
{
Console.WriteLine( "Customer - " + string.Format(
"{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}",
customer.ID, customer.SurName, customer.GivenName,
customer.Street, customer.City, customer.State,
customer.Country, customer.PostalCode,
customer.Phone, customer.CompanyName ) );
foreach ( var contact in customer.Contacts )
{
Console.WriteLine( " Contact - " + string.Format(
"{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}",
contact.ID, contact.SurName, contact.GivenName,
contact.Title,
contact.Street, contact.City, contact.State,
contact.Country, contact.PostalCode,
contact.Phone, contact.Fax ) );
}
}
}
}
}
}
There are some implementation detail differences between the Microsoft .NET Framework Data Provider for SQL Server (SqlClient) and the SAP Sybase IQ .NET Data Provider of which you should be aware.
A new class SAConnectionFactory (implements IDbConnectionFactory) is included. You set the Database.DefaultConnectionFactory to an instance of SAConnectionFactory before creating any data model as shown below:
Database.DefaultConnectionFactory = new SAConnectionFactory();
The major principle of Entity Framework Code First is coding by conventions. The Entity Framework infers the data model by coding conventions. Entity Framework also does lots of things implicitly. Sometimes the developer might not realize all these Entity Framework conventions. But some code conventions do not make sense for SAP Sybase IQ. There is a big difference between SQL Server and SAP Sybase IQ. Every SQL Server instance maintains multiple databases, but every SAP Sybase IQ database is a single file.
If the user creates a user-defined DbContext using the parameterless constructor, SqlClient will connect to SQL Server Express on the local computer using integrated security. The SAP Sybase IQ provider connects to the default server using integrated login if the user has already created a login mapping.
SqlClient drops the existing database and creates a new database when the Entity Framework calls DbDeleteDatabase or DbCreateDatabase (SQL Server Express Edition only). The SAP Sybase IQ provider never drops or creates the database. It creates or drops the database objects (tables, relations, constraints for example). The user must create the database first.
The IDbConnectionFactory.CreateConnection method treats the string parameter "nameOrConnectionString" as database name (initial catalog for SQL Server) or a connection string. If the user does not provide the connection string for DbContext, SqlClient will automatically connect to the SQL Express server on the local computer using the namespace of user-defined DbContext class as the initial catalog. For SAP Sybase IQ, that parameter can only contain a connection string. A database name will be ignored and integrated login will be used instead.
The SQL Server SqlClient API maps a column with data annotation attribute TimeStamp to SQL Server data type timestamp/rowversion. There are some misconceptions about SQL Server timestamp/rowversion among developers. The SQL Server timestamp/rowversion data type is different from SAP Sybase IQ and most other database vendors:
The SQL Server timestamp/rowversion is binary(8). It is does not support a combined date and time value. SAP Sybase IQ supports a data type called timestamp that is equivalent to the SQL Server datetime data type.
SQL Server timestamp/rowversion values are guaranteed to be unique. SAP Sybase IQ timestamp values are not unique.
A SQL Server timestamp/rowversion value changes every time the row is updated.
The TimeStamp data annotation attribute is not supported by SAP Sybase IQ provider.
By default, Entity Framework 4.1 always sets the schema or owner name to dbo which is the default schema of SQL Server. However, dbo is not appropriate for SAP Sybase IQ. For SAP Sybase IQ, you must specify the schema name (GROUPO for example) with the table name either by using data annotations or the Fluent API. Here's an example:
namespace CodeFirstTest
{
public class Customer
{
[Key()]
public int ID { get; set; }
public string SurName { get; set; }
public string GivenName { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public string CompanyName { get; set; }
public virtual ICollection<Contact> Contacts { get; set; }
}
public class Contact
{
[Key()]
public int ID { get; set; }
public string SurName { get; set; }
public string GivenName { get; set; }
public string Title { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
public string PostalCode { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
[ForeignKey( "Customer" )]
public int CustomerID { get; set; }
public virtual Customer Customer { get; set; }
}
[Table( "Departments", Schema = "GROUPO" )]
public class Department
{
[Key()]
public int DepartmentID { get; set; }
public string DepartmentName { get; set; }
public int DepartmentHeadID { get; set; }
}
public class Context : DbContext
{
public Context() : base() { }
public Context( string connStr ) : base( connStr ) { }
public DbSet<Contact> Contacts { get; set; }
public DbSet<Customer> Customers { get; set; }
public DbSet<Department> Departments { get; set; }
protected override void OnModelCreating( DbModelBuilder modelBuilder )
{
modelBuilder.Entity<Contact>().ToTable( "Contacts", "GROUPO" );
modelBuilder.Entity<Customer>().ToTable( "Customers", "GROUPO" );
}
}
}