Posts Tagged Linq-to-Sql

Tribal Knowledge: Linq Entities To Do Your Bidding via the Partial Keyword in C#

TribalKnowledge2Having premade entities created during the Linq process in .Net really comes in handy. But did you know that one can extend those entities further outside of of the generated dbml file? By using the concept introduced in C# 2.0 of a partial class, where one can have business logic methods and other properties which are not generated but are none the less relevant in an Object Oriented fashion it can be achieved.

As mentioned the entities are generated and nothing is worse than having the generator throw away user code placed in the generated file after a regeneration. To allow for generated and user code to coexist hand in hand was accomplished by partial classes. That is where the partial keyword is appended to a class which allows for the class to have a subset of functionality in a different code file. The generated code file resides in one file and the user code lives elsewhere in a different file allowing them co-exist.

I have used this process to put validation logic on an entity. That logic was used to verify that the differing properties which the user had entered values against matched business rules. Those rules could not be entirely expressed in a database schema. It was very Handy!

Example

A user posted a question on the MSDN Forums as to how one could achieve indexing into a Linq generated class entity where each property represented a numerated indexed field. The below code shows the use of a partial class in a separate file which the user could  have an index-able entity.

public partial class Request
{
    public int this[int index]
    {
        get
        {
            int retValue;

            switch ( index )
            {
                case 0: retValue = Val1; break;
                case 1: retValue = Val2; break;
                case 2: retValue = Val3; break;
            }

            return retValue;
        }
        set
        {
            switch (index)
            {
                case 0: Val1 = value; break;
                case 1: Val2 = value; break;
                case 2: Val3 = value; break;
            }
        }
    }
}
Share

Tags: , , ,

Linq vs. DataReader; Visual Examples Showing That Linq Does it in Fewer Lines of Code

Bridges I recently replied to a post in the MSDN C# General forum (one of the best places to get help if you have C# question) where the user was getting data from a DataReader and needed help with null values being returned. What I ended up discovering was differences from old way of getting data from a database in .Net and the new way. Such as the highways above both go to the same location and essentially do the same thing, but the contrast is so much different. Same holds true for .Net Linq and the DataReader.

In the post I recommend the use of the null-coalescing operator to handle the null returned from the database. Erroneously I forgot that the DataReader returns a DBNull which cannot be handled by the null-coalescing operator. GRRR

My mistake was based in the fact that I have become used to the Linq Data Context returning a INullable<T> when dealing with null fields in a database. The the null-coalescing operator can handle that to a tee.

I created my test code and realized that it demonstrated the following

  1. Linq does so much for the developer that what previously took around six plus lines of code boils down to roughly three. It manages so much in the background for the user that it allows for quicker coding to get at data.
  2. The design of Linq handles Nulls differently, or at lease presents them to the end user from the old way processing. Using the null-coalescing operator as shown is a great help.

Here is the example code, first up is Linq and the target operation is to display values from a nullable field in the database called Description. When enumerating over the data rows, if that field happens to be null it reports it or shows the actual data. Next up is the older way of doing it and notice how the user has to handle reader. Note one could check for DBNull in that situation, so the example may not be what you are looking to do in a real system.

GTCDataContext gtc = new GTCDataContext();

var items = gtc.Location_US;

foreach ( var it in items )
   Console.WriteLine( it.Description ?? "not Defined" );

string queryString = "SELECT Description FROM dbo.Location_US;";

using ( SqlConnection connection = new SqlConnection( gtc.Connection.ConnectionString  ))
{
   SqlCommand command = new SqlCommand( queryString, connection );    connection.Open();

   SqlDataReader reader = command.ExecuteReader();

   // Call Read before accessing data.
   while ( reader.Read() )
   {
      Console.WriteLine( (string.IsNullOrEmpty(reader[0].ToString()) == false) ?  reader[0] : "not defined again" );
   }

    // Call Close when done reading.
    reader.Close();
}

Here is the result of the run:

Florida

not Defined

Colorado

California

not Defined

not Defined

not Defined

not Defined

—————————————–

Florida

not defined again

Colorado

California

not defined again

not defined again

not defined again

not defined again

Share

Tags: , , , , ,

Linq: Flatten Tercerary Table Data Problem

Update: Problem has been solved! See answer section at the bottom!

TablesI have run into a problem which I am unable solve using Linq to Sql. I want flattened data from a database which holds knowledge base type information to get as flattened data. My need is to pass it on to a databound list control to display to the user. Shown to the right are the tables which are needed basically an article and associated tags.

The Article table holds the textual articles a summary and each article has an ID which is its primary key. The textual tag(s) which may describe the articles are saved on a one by one basis in the KB_Tag table and like the article table, it has a primary key ID named Tag_ID. Linking the articles to the actual tag(s) is done by the KB_ArticleTag table which have the foreign key relationships back to the Article table and the KB_Tag table.

Download SQL 2005 database backup here.

Linq Tried

My goal is to extract each article summary and its corresponding tags in data form such as -Summary, “tag1 tag2…”-

“Data by Numbers”| “Data”
“Linq overview”    | “Linq Data C#”

But what I get with my Linq query against my databaselooks like this (shown here using the output of LinqPad):


Linq result showing two columns Summary and tags where the tags are IEnumerable of string and not flattened

Here is the query used:

from ar in dc.Articles
   join atgs in dc.KB_ArticleTags on ar.Article_ID equals atgs.Article_ID into iTags
   select new
   {
       ar.Summary,

       tags = from cpt in iTags
              join tgs in dc.KB_Tags on cpt.Tag_ID equals tgs.Tag_ID
               select tgs.Text

   };

Other things Tried

  • Posted to MSDN forums (Flatten FK Tercerary Table)
  • Response from Lawrence Parker suggested using the Aggregate extension:
    from ar in dc.Articles
    join atgs in dc.KB_ArticleTags on ar.Article_ID equals atgs.Article_ID into iTags
    select new
    {
     ar.Summary,
    
     tags = (from cpt in iTags
      join tgs in dc.KB_Tags on cpt.Tag_ID equals tgs.Tag_ID
      select tgs.Text).Aggregate<String, String>
       ( String.Empty,
       ( String prev, String input ) => prev + (prev == String.Empty ? input : ", " + input) )
     }

But unfortunately the Aggragate generates a runtime error “The query operator ‘Aggregate’ is not supported.” by Linq parser.

Answer

Now Lawrence had the right idea with the Aggregate, but the problem lies in the fact that Linq-To-Sql cannot handle that operation on the backend due to the structure being in an IQueryable. The solution is to bring the data to the front end by using the IEnumerable data source. By bring it away from the backend, the data contained can be operated on locally on the client.

from ar in dc.Articles.AsEnumerable()
join atgs in dc.KB_ArticleTags on ar.Article_ID equals atgs.Article_ID into iTags
select new
{
 ar.Summary,

 tags = (from cpt in iTags
   join tgs in dc.KB_Tags on cpt.Tag_ID equals tgs.Tag_ID
   select tgs.Text)
    .Aggregate<String, String>
     ( String.Empty, ( String prev, String input )
      => prev + (prev == String.Empty ? input : ", " + input) )
}
Share

Tags: , ,