Posts tagged ‘Linq-to-Object’

Asp.Net ListView: Linq Allows Creation of Dynamic Hyperlinks Example

(Update 03.28.2012 Cleaned up article and verified it  works with versions 3.5 and 4.0)

The following ASP.Net article details how to create a ListView, bind it to a Linq query and create a dynamic list of hyperlinks to show on an web page in C#. To recreate this, use Visual Studio (VS2008/VS2010/VS 11) and create a default Asp.Net Web Application project all working in the default page setup.

On the screen there will be a button which will cause a refresh to the page when pressed. On that refresh the ListView will be cleared of all data and the button disabled to show that the control has not been rebound to actual data.

Here is what it will look like on its first run with a button and dynamically created html links in a list.

Example showing button and dynamic links

HTML: which has the button and the ListView. We will hand create the LayoutTemplate and the ItemTemplate. Our goal is to have a list of links and dynamically create them. The ItemTemplate is expecting to be bound to Url field and UrlText which Linq will nicely create for us in the code behind.

<asp:Button ID="btnClear" runat="server" Text="Clear" /> 

<br /> 
<asp:ListView ID="lvPanelGroup1" runat="server">     
                <asp:PlaceHolder runat="server" ID="itemPlaceholder" />             
            <asp:HyperLink ID="HyperLink1"                            
                           NavigateUrl='<%# Eval("Url") %>'                            
                <%# Eval("UrlText") %>

C#:The following is the code in the PageLoad. Its job is to either bind the data in the ListView using Linq or clear the ListView of all items and disable the control. Notice the Linq code where we create our object to have a Url field and UrlTextField which the ListView will use.

protected void Page_Load(object sender, EventArgs e) 
    if (IsPostBack == false) 
        List<string> Titles = new List<string>()        
            { "Title 1", "Title 2", "Title 3", "Fini"};     

        int index = 0;     

        var links = from item in Titles                 
                    select new                 
                                Url = "~Default.aspx?articleid=" + (++index).ToString(),                     
                                UrlText = item                 
        lvPanelGroup1.DataSource = links;     
        btnClear.Enabled = true; 
    else // Clear the ListView 
        lvPanelGroup1.DataSource = null;     

        // The rebinding will clear the ListView.     
        btnClear.Enabled = false; 

Here is what happens after a button press:

What happens after Clear is pressed.

Hope this helps those learning about the ListView and Linq.


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

       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
     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.


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

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

C# Regex MatchCollection Meets Linq

Here is a code snippet which accomplishes these following goals:

  • It marries a C# Regular Expression MatchCollection to a property list using Linq.
  • It uses a Regex Pattern which creates named capture groups which Linq can easily exploit in the join of two data lists.

Let me show you the code. Don’t get hung up on the pattern or what it is doing. What needs to be known is that the pattern places the data matched into Named Capture Groups of Key and Value. The actual Key value corresponds to a property on a real class. Using reflection we will find that property on the class and link its property name to the value stored. That will allow us to change that properties value on the class from the Value we get from the regex match.

The goal of the Linq code is to join into another list, which is the list of properties from the class and the commonality is the PropertyInfo.Name found in that list. Once that data is joined a new object will be created which will have the actual property object and the value of Value. That new list will allow the following operations to set target properties value to Value of the match in Match collection.

public static T ASCIISerializeOut<T>( string targetSerialized )
     where T : new()

     T targetInstance = new T();

     string pattern = string.Format( @"(?<Key>[^{0}]*)(?:{0})(?<Value>[^{1}]*)(?:{1}?)",
           Seperators.cnKVPSeperator,   // "±"
           Seperators.cnSeperator );    // "¶"

     MatchCollection mcKVPs = Regex.Matches( targetSerialized,
                                             RegexOptions.Compiled );

     var kvps = from Match m in mcKVPs
                where mcKVPs != null
                where mcKVPs.Count > 0
                join prp in GetPublicProperties<T>() on m.Groups["Key"].Value equals prp.Name
                select new
                    prop  = prp,
                    Value = m.Groups["Value"].Value ?? string.Empty

     foreach (var item in kvps)
         item.prop.SetValue( targetInstance, item.Value, null );

     return targetInstance;


 /// <summary>
 /// Return all public properties which are of string type from T class.
 /// </summary>
 public static IEnumerable<PropertyInfo> GetPublicProperties<T>()
     return from p in typeof( T ).GetProperties()
            where p.PropertyType == typeof( string )
            select p;
  • Line 01: The function takes in text such as "AProp±AValue¶BProp±BValue" which needs to be serialized into a newly created class of type T. The first item in the pattern is the property name AProp followed by a seperator ± then the value of the property AValue and finally a key value seperator: ¶. our regex will create individual matches for each of the key value pairs.
  • Line 07: This pattern when used will get key and value pair combinations and place them in named groups of Key and Value of the match.
  • Line 11: Get all the key/value pair combinations into the match collection.
  • Line 15: Linq starts here: We define a Var object kvps (key value pairs) which will use /loop each match from the match collection.
  • Line 16: Make sure the collection is not null.
  • Line 17: Make sure there are one or more matches.
  • Line 18: Get all the public properties of class T and make a join to our collection data. Key should match the property Name.
  • Line 19: Each match found within the property where the names are the same will create this new object below with two properties.
  • Line 21: Save the actual property object, we need that later to load data.
  • Line 22: Get the value out of the Value group and save that as well. Note, if it is null, just use string.Empty. Thanks Null Coalescing operation (??).
  • Line 25: Now for each var object created enumerate through it and load the target values into our newly minted class object of T.
  • Line 26: Set the target item’s property to the value found from the regex matches.
  • Line 28: Return the new object with the original text data serialized in.
  • Line 35: Return an enumeration of all generic string properties of the type T.

For completeness see my post entitled A C# ASCII Serializer Generic Method for Class Objects which has the actual downloaded project and working test example. (Post coming soon!)