Entity Framework Core Support

On this page

dotConnect for Google BigQuery supports Entity Framework Core.

What is Entity Framework Core?

Entity Framework Core is a lightweight, extensible, and cross-platform object-relational mapper (ORM) for .NET. It enables developers to work with data using .NET objects and LINQ queries instead of writing most of the data access code manually. Entity Framework Core applications provide the following benefits:

Applications work in terms of an application-centric object model instead of a relational storage schema.
Language-integrated query support provides compile-time syntax validation for queries against the model.
Mappings between the object model and the storage schema can change without changing the application code.
The same model can be used with different data sources.

Supported Entity Framework Core Versions

Entity Framework Core support is provided by the Devart.Data.BigQuery.EFCore NuGet package. The same package is released in several builds, one for each supported Entity Framework Core version. The build is identified by the last part of the package version: it matches the major version of Entity Framework Core. The package version without the fourth part is intended for Entity Framework Core 3.1.

Entity Framework Core	Package version	Supported platforms
3.1	X.Y.Z	.NET Framework 4.6.1 and higher, .NET Core 3.1 and higher
8	X.Y.Z.8	.NET 8 and higher
9	X.Y.Z.9	.NET 8 and higher
10	X.Y.Z.10	.NET 10
11	X.Y.Z.11	.NET 11

X.Y.Z is the version of dotConnect for Google BigQuery. For example, for Entity Framework Core 9 and dotConnect for Google BigQuery 1.2.3, use the Devart.Data.BigQuery.EFCore package version 1.2.3.9.

By default, NuGet installs the latest package version, which is the build for the latest supported Entity Framework Core version. If your application uses an earlier Entity Framework Core version, specify the package version explicitly when adding the package. To install the package for Entity Framework Core 9, run one of the following commands, replacing X.Y.Z.9 with the required package version:


Install-Package Devart.Data.BigQuery.EFCore -Version X.Y.Z.9 dotnet add package Devart.Data.BigQuery.EFCore --version X.Y.Z.9

Entity Framework Core Support Key Features

Full CRUD (Create, Retrieve, Update, Delete) support
Wide support for LINQ to Entities
Database-First (reverse engineering of BigQuery tables to a model with the Scaffold-DbContext command)
Code-First, including migrations
Support for BigQuery transactions
Wide support for BigQuery data types; see Entity Framework Core Data Type Mapping

Using the Entity Framework Core Provider

Add the Devart.Data.BigQuery.EFCore NuGet package of the version that corresponds to your Entity Framework Core version to your project (see Supported Entity Framework Core Versions).

To register the Entity Framework Core provider and set a connection string, override the OnConfiguring method of your DbContext class descendant.


protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseBigQuery(@"Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=my-project;DataSet Id=my_dataset;License Key=**********"); }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
  optionsBuilder.UseBigQuery(@"Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=my-project;DataSet Id=my_dataset;License Key=**********");
}

The BigQuery dataset specified in the DataSet Id connection string parameter acts as the database of the model. DataSet Id is required for Entity Framework Core.

Database-First

Database-First is supported via the Scaffold-DbContext command in Package Manager Console or the dotnet ef dbcontext scaffold command in .NET CLI.


Scaffold-DbContext "Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********" Devart.Data.BigQuery.EFCore -OutputDir Models

Scaffold-DbContext "Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********" Devart.Data.BigQuery.EFCore -OutputDir Models

Code-First

Code-First is supported with the EnsureCreated method and migrations. EnsureCreated creates the dataset (if it doesn't exist) and the tables of the model. EnsureDeleted isn't supported, because deleting the dataset would delete all tables in it, including the ones that don't belong to the model.

Mapping

The mapping supported by Entity Framework Core is described in detail in Entity Framework Core documentation. dotConnect for Google BigQuery supports the standard Entity Framework Core mapping methods and attributes with the following specifics.

Fluent API method (attribute)	BigQuery support
ToTable(name) ([Table])	Supported. The table is created in the dataset specified in the DataSet Id connection string parameter.
ToTable(name, schema) ([Table(Schema = ...)])	Supported. The schema is a BigQuery dataset of the same project. EnsureCreated and migrations create the dataset if it doesn't exist.
HasColumnName ([Column])	Supported.
HasColumnType ([Column(TypeName = ...)])	Supported. Use GoogleSQL type names, for example, NUMERIC(12, 2), BIGNUMERIC, STRING(100), JSON, GEOGRAPHY, DATE, TIMESTAMP. For more information, see Entity Framework Core Data Type Mapping.
HasMaxLength ([MaxLength], [StringLength])	Supported for strings and byte arrays: the column is created as STRING(n) or BYTES(n).
HasPrecision ([Precision])	Supported for System.Decimal: the column is created as NUMERIC(p, s). NUMERIC allows precision up to 38 and scale up to 9; for greater values, specify BIGNUMERIC(p, s) with HasColumnType. In Entity Framework Core 3, use HasColumnType.
IsRequired ([Required])	Supported: the column is created as NOT NULL.
HasDefaultValue, HasDefaultValueSql	Supported: the column is created with the DEFAULT clause.
HasKey ([Key])	Supported: the key is created as PRIMARY KEY (...) NOT ENFORCED. BigQuery doesn't check the uniqueness of key values.
HasIndex ([Index])	Allowed in the model, but no index is created in the database: BigQuery has no indexes. Unique indexes and alternate keys aren't created either.
HasOne / HasMany / HasForeignKey	Relationships work in the model and in LINQ queries, but foreign keys aren't created in the database, and referential integrity isn't checked by the server.
HasComputedColumnSql	Not supported: BigQuery has no computed columns.
HasSequence, UseHiLo	Not supported: BigQuery has no sequences.
ValueGeneratedOnAdd for keys	Not supported: BigQuery doesn't generate values; assign key values in the application.

Migrations

Migrations are supported with the following BigQuery restrictions:

Migration statements are executed without a transaction, because BigQuery doesn't allow DDL statements on permanent tables inside a transaction. If a migration fails in the middle, the statements that were already executed aren't rolled back.
A NOT NULL column can't be added to an existing table, and an existing column can't be made NOT NULL. Add such columns as nullable.
An existing column type can be changed only to a wider type (for example, NUMERIC to BIGNUMERIC or STRING(10) to STRING(20)). An ALTER COLUMN statement can also drop NOT NULL and set or drop the default value.
A table can't be moved to another dataset by renaming it.

Deploying an Entity Framework Core Project

When deploying Entity Framework Core projects, make sure that the Devart.Data.dll, Devart.Data.SqlShim.dll, Devart.Data.BigQuery.dll, and Devart.Data.BigQuery.EFCore.dll assemblies are available for your application. If you reference the provider via NuGet packages, these assemblies are copied to the output folder automatically. You don't need to register the provider in the machine.config or app.config file.

Limitations

BigQuery doesn't enforce primary keys and doesn't support foreign keys, indexes, computed columns, and sequences. For more information, see the Mapping section.
BigQuery doesn't generate values, so key values must be assigned by the application.
BigQuery is an analytical data warehouse: DML operations (INSERT, UPDATE, DELETE) are subject to BigQuery quotas and are much slower than in transactional databases. Entity Framework Core is best suited for reading data and for moderate volumes of changes.

[email protected]