Share via


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.

    }

}

Comments

  • Anonymous
    January 30, 2014
    Thanks exactly what I was looking for. Did not try to run your code but got the idea how to do it.