How to use LINQ to SQL without using the designer generated classes
Sometimes people wonder if it is possible to use LINQ to SQL without having to use the designer and the automatically
generated .dbml files and classes. The short answer is yes, and it is not even very hard to do.
In the end it may be worth using the designer, for example, if you have many tables with many columns and many associations
then it is nice to have someone (the designer) to create the classes and fields and associations for you.
In other cases if you, for example, have a smaller set of tables then perhaps it is easier to do this by yourself.
For example, if you make a change in you database, then you need to regenerate the .dbml file in order to see the changes.
If you have full control over the datatable classes, then changing a datatable column name mapping is super simple.
Again, I prefer the showing by example method.
Beware that this is a really simple scenario, but it shows how easy it is to get started.
So, we will have two tables, one with Artist and one with Records. The association between the two should be obvious J
Create the tables and insert some rows like this;
-- Create Artis table
create table Artists(aid int primary key, name nvarchar(50) not null, alias nvarchar(50))
-- Create Record table
create table Records(rid int primary key, aid int not null, name nvarchar(50) not null, releaseyear int not null)
-- Insert some rows.
insert into Artists values (1, 'Tommy Rocker', 'The Rock')
insert into Artists values (2, 'The Tables', null)
insert into Records values (1, 1, 'Rockfest', 2001)
insert into Records values (2, 1, 'Rockfest II', 2005)
insert into Records values (3, 2, 'Column Love', 1998)
insert into Records values (4, 2, 'Key party', 2003)
insert into Records values (5, 1, 'The Debut', 1995)
--drop table Records
--drop table Artists
If we now would create a .dbml form this it would name the columns after the original columns, i.e. “aid” for the artist id in the Record table.
Not very readable. But this may be a legacy database, so changing the name here may not be an option since many other applications may rely on the column name to be “aid”
We can change this in the designer, but if we regenerate the .dbml file, then these changes will be lost. We will take care of this too.
So, create a new C# console application. Include a reference to System.Data.Linq.
Above the generated class Program, create the following three classes, one for each table and one for our DataContext, inherited from, unsurprisingly, DataContext.
The code should be self-explanatory, but in short we are creating one class per table in the database and a datacontext for the database.
We map the table and column names to what we fell is appropriate, we tell the Artist class that it should hold a set of Records associated with this artist based on the artist id.
We also create a DataContext class that takes a connection string to our server/database and include the table objects (Artists and Records)
#region Tables and DataContext classes
// Here we are saying that the class Record maps to the datbase table Records
[Table(Name = "Records")]
public class Record
{
[Column(Name = "rid", IsPrimaryKey = true)] // Column "rid" in database is mapped to "RecordId"
public int RecordId;
[Column(Name = "aid", CanBeNull = false)]
public int ArtistId;
[Column(Name = "name", CanBeNull = false)]
public string RecordName;
[Column(Name = "releaseyear", CanBeNull = false)]
public int Year;
// This allows us to get the related Artist for the Record at hand
private EntityRef<Artist> _Artist;
[Association(Storage = "_Artist", ThisKey = "ArtistId", OtherKey = "ArtistId")]
public Artist Artist
{
get { return this._Artist.Entity; }
set { this._Artist.Entity = value; }
}
}
[Table(Name = "Artists")]
public class Artist
{
[Column(Name = "aid", IsPrimaryKey = true)]
public int ArtistId;
[Column(Name = "name", CanBeNull = false)]
public string ArtistName;
[Column(Name = "Alias")]
public string ArtistAlias;
// Here we create the EntitySet that lets us have an Artist.Record list, this will be populated automatically
// since we have mapped "ArtistId" in this class with the "ArtistId" in the Record class.
private EntitySet<Record> _Records;
[Association(Storage = "_Records", ThisKey = "ArtistId", OtherKey = "ArtistId")]
public EntitySet<Record> Records
{
get { return this._Records; }
set { this._Records.Assign(value); }
}
}
public class ArtistRecordDataContext : DataContext
{
// Inherit from the DataContext class.
// One constructor accepts an SqlConnection, or we can pass the connectionstring directly.
public ArtistRecordDataContext(string cs) : base(cs) { }
public ArtistRecordDataContext(SqlConnection con) : base(con) { }
// Create the tables for the database (datacontext)
public Table<Artist> Artist;
public Table<Record> Record;
}
#endregion
class Program
{
static void Main(string[] args)
{
...
That is it really. So lets test it. In the main method, add the following code:
// Create the connection that will be passed to our DataContext class
String cs = @"Data Source=<server>;Initial Catalog=<database>;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(cs);
// Create an instance of our datacontext class
ArtistRecordDataContext dc = new ArtistRecordDataContext(con);
Console.WriteLine("\nArtists that has an alias:");
// Create query to get all artist that has an alias (should only be one at first run)
var artistQuery = from a in dc.Artist where a.ArtistAlias != null select a;
// Execute query
foreach (var artist in artistQuery)
{
Console.WriteLine("\t{0} aka {1}", artist.ArtistName, artist.ArtistAlias);
}
Console.WriteLine("\nArtists and their records:");
// Create query that will select all artist, group them by the artistId and then display their records
var recordsQuery = from a in dc.Artist
group a by a.ArtistId into artistGroup
select artistGroup;
// Execute query
foreach (var ag in recordsQuery)
{
// We are iterating over each group of artists, so we need to get the artist for the current group
foreach (Artist artist in ag)
{
Console.WriteLine("{0}", artist.ArtistName);
// For each artist in the group, get their records.
foreach (Record record in artist.Records)
{
Console.WriteLine("\t{0}, released in {1}", record.RecordName, record.Year);
}
}
}
Again, this should be pretty self explanatory, first we get a list of all artist that has an alias, should be one, and then we get all artist and their records.
So the output should be:
Artists that has an alias:
Tommy Rocker aka The Rock
Artists and their records:
Tommy Rocker
Rockfest, released in 2001
Rockfest II, released in 2005
The Debut, released in 1995
The Tables
Column Love, released in 1998
Key party, released in 2003
If you, for example, wish to set or change an alias or add a record, it is as simple as this:
// We can also easily update records, for example, setting an alias for the with id 2
Artist getArtist = dc.Artist.First(x => x.ArtistId == 2);
getArtist.ArtistAlias = "New Table Alias";
// We can, for example, add a new record
Record newRecord = new Record { ArtistId = 2, RecordId = 10, RecordName = "The New Album", Year = 2010 };
dc.Record.InsertOnSubmit(newRecord);
dc.SubmitChanges();
If you now rerun the code that selects the alias and the artist records, the output should be:
Artists that has an alias:
Tommy Rocker aka The Rock
The Tables aka New Table Alias
Artists and their records:
Tommy Rocker
Rockfest, released in 2001
Rockfest II, released in 2005
The Debut, released in 1995
The Tables
Column Love, released in 1998
Key party, released in 2003
The New Album, released in 2010
Easy as that J
Complete code below.
Some references:
"Attribute-Based Mapping (LINQ to SQL)"
https://msdn.microsoft.com/en-us/library/bb386971.aspx
"DataContext Class"
https://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.aspx
Full code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq.Mapping;
using System.Data.Linq;
using System.Data.SqlClient;
#region Table, Support classes and DataContext classes
// Here we are saying that the class Record maps to the datbase table Records
[Table(Name = "Records")]
public class Record
{
[Column(Name = "rid", IsPrimaryKey = true)] // Column "rid" in database is mapped to "RecordId"
public int RecordId;
[Column(Name = "aid", CanBeNull = false)]
public int ArtistId;
[Column(Name = "name", CanBeNull = false)]
public string RecordName;
[Column(Name = "releaseyear", CanBeNull = false)]
public int Year;
// This allows us to get the related Artist for the Record at hand
private EntityRef<Artist> _Artist;
[Association(Storage = "_Artist", ThisKey = "ArtistId", OtherKey = "ArtistId")]
public Artist Artist
{
get { return this._Artist.Entity; }
set { this._Artist.Entity = value; }
}
}
[Table(Name = "Artists")]
public class Artist
{
[Column(Name = "aid", IsPrimaryKey = true)]
public int ArtistId;
[Column(Name = "name", CanBeNull = false)]
public string ArtistName;
[Column(Name = "Alias")]
public string ArtistAlias;
// Here we create the EntitySet that lets us have an Artist.Record list, this will be populated automatically
// since we have mapped "ArtistId" in this class with the "ArtistId" in the Record class.
private EntitySet<Record> _Records;
[Association(Storage = "_Records", ThisKey = "ArtistId", OtherKey = "ArtistId")]
public EntitySet<Record> Records
{
get { return this._Records; }
set { this._Records.Assign(value); }
}
}
public class ArtistRecordDataContext : DataContext
{
// Inherit from the DataContext class.
// One constructor accepts an SqlConnection, or we can pass the connectionstring directly.
public ArtistRecordDataContext(string cs) : base(cs) { }
public ArtistRecordDataContext(SqlConnection con) : base(con) { }
// Create the tables for the database (datacontext)
public Table<Artist> Artist;
public Table<Record> Record;
}
#endregion
class Program
{
static void Main(string[] args)
{
// Create the connection that will be passed to our DataContext class
String cs = @"Data Source=<server>;Initial Catalog=<database>;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(cs);
// Create an instance of our datacontext class
ArtistRecordDataContext dc = new ArtistRecordDataContext(con);
Console.WriteLine("\nArtists that has an alias:");
// Create query to get all artist that has an alias (should only be one at first run)
var artistQuery = from a in dc.Artist where a.ArtistAlias != null select a;
// Execute query
foreach (var artist in artistQuery)
{
Console.WriteLine("\t{0} aka {1}", artist.ArtistName, artist.ArtistAlias);
}
Console.WriteLine("\nArtists and their records:");
// Create query that will select all artist, group them by the artistId and then display their records
var recordsQuery = from a in dc.Artist
group a by a.ArtistId into artistGroup
select artistGroup;
// Execute query
foreach (var ag in recordsQuery)
{
// We are iterating over each group of artists, so we need to get the artist for the current group
foreach (Artist artist in ag)
{
Console.WriteLine("{0}", artist.ArtistName);
// For each artist in the group, get their records.
foreach (Record record in artist.Records)
{
Console.WriteLine("\t{0}, released in {1}", record.RecordName, record.Year);
}
}
}
// We can also easily update records, for example, setting an alias for the with id 2
Artist getArtist = dc.Artist.First(x => x.ArtistId == 2);
getArtist.ArtistAlias = "New Table Alias";
// We can, for example, add a new record
Record newRecord = new Record { ArtistId = 2, RecordId = 10, RecordName = "The New Album", Year = 2010 };
dc.Record.InsertOnSubmit(newRecord);
dc.SubmitChanges();
// Rerun the code with the 3 lines above (the Record insert) commented out (otherwise there will be
// a duplicate key issue) to verify that the changes has taken effect.
}
}