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)”


http://msdn.microsoft.com/en-us/library/bb386971.aspx


“DataContext Class”


http://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 (1)

  1. Sohail says:

    Thanks exactly what I was looking for. Did not try to run your code but got the idea how to do it.