LinqToSql - Helmut-Ortmann/EnterpriseArchitect_hoTools GitHub Wiki

LINQ to SQL

You find standard SQL somehow old, limited, difficult to learn and a lot of dialects? You think a Compiler can find a lot of your typos, Then give LINQ to SQL a try. You have two possibilities:

  • LINQPad as an interactive query and debugging tool, database independent, C#, F#, VB
  • Develop your Add-Ins with LINQ to SQL,C#, F#, VB, see AddInSimple

The best: All the solutions are database independent, and you can explore them in hoTool, Project AddInSimple.

Add-In with LINQ to SQL

You find two examples to tinker with in AddInSimple. In essence:

  • Determine Database and connection string
  • Run LINQ query to DataTable
  • Output DataTable to EA Model Search
  • Enjoy

The results

Try develop your Add-In with the example AddInSimple delivered with in hoTools and the Example Database.

Control code

            // Advanced LINQ to SQL example
            case MenuShowRunLinq2DbAdvanced:
                // get connection string of repository
                connectionString = LinqUtil.GetConnectionString(repository, out provider);

                // Run LINQ query to dataTable
                dt = LinqUtil.RunLinq2DbAdvanced(provider, connectionString);
                // Make EA xml
                OrderedEnumerableRowCollection<DataRow> rowsAdvanced = from row in dt.AsEnumerable()
                    orderby row.Field<string>(dt.Columns[0].Caption)
                    select row;
                xml = Util.MakeXml(dt, rowsAdvanced);

                // Output to EA
                repository.RunModelSearch("", "", "", xml);
                break;

Query code

  • Query "countObjectTypes" for number of objects

  • Query "q" for all object_types, grouped with some results for each object_type

  • Query countReq for number of requirements

  • Query "q1" for all requireents, grouped by stereotype with some results for each requirement type

  • var sum = q.Concat(q1); // combine the two queries

  • // return as table to easily convert to EA xml by xml = Util.MakeXml(dt, rowsAdvanced);

  • return sum.ToDataTable();

     public static DataTable RunLinq2DbAdvanced(IDataProvider provider, string connectionString)
 {
  using (var db = new DataModels.EaDataModel(provider, connectionString))
  {
      // Total amount of Object_Types
      var countObjectTypes = db.t_object.Count();

      // All object_types summary:
      // - Type
      // - Count
      // - Percentage
      // - Total count of object_types
      var q =
      (from c in db.t_object.AsEnumerable()
          group c by c.Object_Type into g
          orderby g.Key

          select new
          {
              Type = $"{g.Key}",
              Prozent = $"{ (float)g.Count() * 100 / countObjectTypes:00.00}%",
              Count = g.Count(),
              Total = countObjectTypes
          });


      // Requirement summary:
      // - Type
      // - Count
      // - Percentage
      // - Total count of requirements
      var countReq = db.t_object.Where(e => e.Object_Type == "Requirement").Count();
      var q1 =
      (from c in db.t_object.AsEnumerable()
          where c.Object_Type == "Requirement"
          group c by c.Stereotype into g
          orderby g.Key

          select new
          {
              Type = $"Req:<<{g.Key}>>",
              Prozent = $"{ (float)g.Count() * 100 / countReq:00.00}%",
              Count = g.Count(),
              Total = countReq
          });

      // Concatenate Object Types with Requirement Types
      var sum = q.Concat(q1);

      // return as DataTable
      return sum.ToDataTable();

  }
}

Useful hoTools functions

The functions and classes are located in project: 'hoLinqToSql'

You can see them running in AddInSimpl.

Signature Description
DataModelEA1.generated.cs DataModel of EA for LINQ to SQL (from linq2db)
string = GetConnectionString(repository, out provider) Get provider and connection string from EA
xml = Util.MakeXmlFromDataTable(dt) Get EA XML from DataTable
xml = Util.MakeXml(dt, rowsAdvanced) Get EA XML from DataTable and the query rowsAdvanced (only for advanced usages, use Util.MakeXmlFromDataTable(dt))
Class: LinqPad Abstracts access to LINQPad (needs LINQPad license)
Class: LinqPad Collect the EA context and pass it to LinQPad (Context, Element, Selelected, Tree Selected
Class: LinqPad.Run Run LINQPad for a query and formats it as html, csv or text.
Class: LinqPad.ReadHtml Reads table 't1' from html and returns a DataTable (ready to output in EA Model Search Window)

Get rowsAdvanced

Usually you create a DataTable and converts it via 'xml = Util.MakeXmlFromDataTable(dt)' to EA xml. Here you may do some additional finishing.

                // Make EA xml from DataTable
                OrderedEnumerableRowCollection<DataRow> rowsAdvanced = from row in dt.AsEnumerable()
                    orderby row.Field<string>(dt.Columns[0].Caption)
                    select row;
                // Convert query into xml
                xml = Util.MakeXml(dt, rowsAdvanced);
                // Output to EA
                repository.RunModelSearch("", "", "", xml);

You find everything in hoTools Project AddInSimple! Search for "case MenuShowRunLinq2DbAdvanced:".

Trouble Shooting

Sometimes you get weird error messages. Something about SQL and LINQ to SQL can't...

Don't worry. LINQ to SQL tries to do a lot with SQL. Sometimes it easy doesn't add up. If you encounter such behavior: Force LINQ to work in memory.

// Process in memory to avoid SQL Access (e.g. string.Join(", ", isn't supported by Access SQL)
var tempRead = read.ToList().AsEnumerable();                 //.Dump("Read in memory");
var tempWrite = write.ToList().AsEnumerable();               //.Dump("Write in memory");;
var combined = tempRead.Concat(tempWrite).AsEnumerable();    //.Dump("Combined in memory");

It's simple. Just write your query to list by "write.ToList().AsEnumerable();". After that, you can work with the query, and you are sure that LINQ to SQL doesn't translate it into SQL.

Summary

LINQ to SQL is the solution for complex queries:

  • Interactive query tool LINQPad
  • In your Add-In to easily handle complex queries database independent
  • Database independent
  • IntelliSence and AutoComplete
  • Compiler verifies against EA Datbase (no typos)
  • Comprehensive debug features

References