FoxSharp

LINQ Operators – Part X

2009-07-31T05:14:00.001+03:00

I have been busy with a series of tasks and couldn't find time to complete operators section. Although I had a safari account via university subscription and could read it online, I ordered a great book "C# 3.0 in a nutshell – O'Reilly – Joseph Albahari & Ben Albahari". Amazon estimate was I would get it sometime after August 11th but to my surprise I got it yesterday and maybe that has motivated me to spare extra time and write today's (well tonight's) blog. That is not the first book I read about C# and possibly won't be the last but I must admit I liked it very much.

Conversion operators:

OfType: Returns the items from a sequence that are of a given type. This is especially useful with object collections where the collection can have multiple different types and ArrayList is a typical sample.

ArrayList al = new ArrayList();
al.Add(5);
al.Add(7);
al.Add("Jim");
al.Add("Frank");
al.AddRange( Customers.Where( c => c.Country == "USA" ).ToList());

al.OfType<int>().Dump("Integers");
al.OfType<string>().Dump("Strings");
al.OfType<Customers>().Dump("Customers");

and here is the partial output in LinqPad:

With generic lists it is unlikely that you would need OfType.

Cast: Cast returns a sequence attempting to cast each member to a given type. If it can't cast one ten it throws an error. The difference between OfType and Cast is that, Cast throws an error if it encounters an incompatible type for casting. OfType, on the other hand, ignores incompatible types and simply returns an empty sequence if it cannot cast any member.

ArrayList al = new ArrayList();
al.Add(5);
al.Add(7);
al.Cast<int>().Dump("Integers");
al.OfType<string>().Dump("Strings");

If we try the OfType<string>() with Cast<string>() we will get an exception. Probably, Cast was added to support ArrayList and I can't think of a good usage for it.

AsEnumerable: Upcasts a sequence to an IEnumerable sequence. Converting to an Enumerable effectively makes the sequence local and then you can apply methods that are not supported on a remote (IQueryable) source. For example Linq To SQL doesn't support Last() but you could make the sequence local with AsEnumerable() and apply Last(). This nice sample is borrowed from LinqPad samples:

Regex wordCounter = new Regex (@"\b(\w|[-'])+\b");

// Click the 'SQL' tab below after running this query -
// notice that only the topic filtering
// predicate executes on SQL Server.

var query = MedicalArticles
           .Where (article => article.Topic == "influenza")
           .AsEnumerable()
           .Where (article => wordCounter.Matches (article.Abstract).Count < 100);

query.Dump();

The sample is getting medical articles whose topic is "influenza" and abstract is less than 100 words. Getting articles whose topic is "influenza" is trivial and the part before AsEnumerable() does that. That part is converted to this SQL:

-- Region Parameters
Declare @p0 VarChar(9) Set @p0 = 'influenza'
-- EndRegion
Select [t0].[ID], [t0].[Topic], [t0].[Abstract]
FROM [MedicalArticles] As [t0]
WHERE [t0].[Topic] = @p0

The tricky part is counting the words. To count words regular expression is used and that wouldn't execute on Linq To SQL. Applying AsEnumerable() converts the sequence to a local one and makes it possible to count the words using regular expression.

AsQueryable: Converts a sequence to an IQueryable either by downcasting or by creating an IQueryable wrapper. Converted to an IQueryable you can execute the query locally or remotely.

ToArray and ToList: Convert a sequence to an array or generic list respectively. The generated array or list is a snapshot taken at the moment the query is run. That is particularly useful when a query needs to be executed often and a snapshot is sufficient.

ToDictionary and ToLookup: These operators create a snapshot dictionary of a given sequence. A dictionary is a key, element pair and you can access a member using the key as a indexer. ToDictionary creates a dictionary where sequence has a unique entry per key (typically a primary key as in a table). ToLookup on the hand looks like foreign key and there can be multiple values for the same key in a sequence. The key is used as an indexer in both.

var cusDict = Customers.ToDictionary( c => c.CustomerID, c => c );
cusDict["BONAP"].Dump();

gives this result (note how we use the unique CustomerID as a key and the particular value to "BONAP" to access a member in dictionary via indexer):

var clookup = Orders.ToLookup( o => o.CustomerID, o => o);

clookup["BONAP"].Dump("Orders of customer BONAP");

yields:

Other operators:

SequenceEqual: Also accepted as a quantifier operator this operator compares two sequences and returns true if the sequences have identical elements and in the same order.

var s1 = "ClintEastwood".ToLower().OrderBy(l => l);
var s2 = "OldWestAction".ToLower().OrderBy(l => l);
s1.SequenceEqual(s2).Dump("An anagram from wikipedia");

This concludes the standard Linq operators. There are other extension methods that are not yet available in .Net 3.5 and since it is extensible we should expect more in the future. Happy LINQinq.

LINQ Operators – Part IX

2009-07-03T02:53:00.001+03:00

Element operators:

First, FirstOrDefault: Used to return the first element from a sequence. T-SQL counterpart is "top 1".

Last, LastOrDefault: Used to return the last element from a sequence. T-SQL counterpart is "top 1 … order by desc"

Single, SingleOrDefault; Used the return the single result from a sequence. Similar to First/FirstOrDefault but this one throws an exception if there are more than 1 results.

Using these methods you can either get First, Last or Single element from a sequence ( like a "locate" without any additional clause ). Or you can use a boolean predicate to filter "for" a match. Sometimes there wouldn't be a match to the predicate you supplied and no element would be returned from a sequence. That would cause an exception to be thrown unless you have used the FirstOrDefault, LastOrDefault or SingleOrDefault. "Or default" versions return an element from sequence a default( TSource ) – which is "null" for reference types like string, Customer and blank for value types (generally 0).

Customers.Where( c => c.Country == "UK")

Returns a sequence of Customers whether it may be 0 or more Customers. We could get the first UK customer as a single element of Customers type using First like this:

Customers.Where( c => c.Country == "UK").First()

or:

Customers.First( c => c.Country == "UK" )

Since the result is now an element we could directly get its properties if we wanted to like:

Customers.First( c => c.Country == "UK" ).CompanyName

The problem with First() is that if the country was something like "Turkey" (there is no customer from Turkey in Northwind sample database) then the sequence that First() would operate on would have no elements to choose from hence an exception would be thrown. FirstOfDefault() on the other hand, would return a default Customers element:

Customers.First( c => c.Country == "Turkey" ) // throws exception

Customers.FirstOrDefault( c => c.Country == "Turkey" ) // default Customers element - null

Last and LastOrDefault works like First/FirstOrDefault but is not supported in Linq To SQL.

int[] numbers = {3,8,6,8,3,4,7};
numbers.Last( n => n % 2 == 0).Dump(); // 4

Single and SingleOrDefault is useful when we are getting data using a unique key (typically a primary key):

string id1 = "BONAP";
string id2 = "XXXXX";

Customers customer1 =
Customers
.SingleOrDefault( c => c.CustomerID == id1);

Customers customer2 =
Customers
.SingleOrDefault( c => c.CustomerID == id2);

( customer1 == null
? "No such customer exists."
: customer1.CompanyName ).Dump("Key:" + id1);

( customer2 == null
? "No such customer exists."
: customer2.CompanyName ).Dump("Key:" + id2);

ElementAt, ElementAtOrDefault: Similar to other element operators, returns element at Nth position (or default(T) with ElementAtOrDefault when there is no element at given position). Not supported in Linq To SQL. You can think of it as record number or array position:

IEnumerable<Customers> cus = Customers.AsEnumerable();

cus.ElementAtOrDefault( 34 ).Dump("34th"); // HILAA
cus.ElementAtOrDefault( 134 ).Dump("134th"); // null

DefaultIfEmpty: Is used for returning a default(TSource) when the sequence is empty (normally it would return a "sequence" if existed). Used in "left join" type queries.

Customers.SelectMany (
   c => c.Orders.DefaultIfEmpty (),
   (c, o) => new
   {
      CustomerID = c.CustomerID,
      CompanyName = c.CompanyName,
      oid = ((Int32?)(o.OrderID) == null) ? 0 : o.OrderID,
      OrderDate = o.OrderDate
   }
)

It supports a parameter of TSource defining "empty" value. Here is a sample from MSDN documentation:

class Pet
{
    public string Name { get; set; }
    public int Age { get; set; }
}

public static void DefaultIfEmptyEx2()
{
    Pet defaultPet = new Pet { Name = "Default Pet", Age = 0 };

    List<Pet> pets1 =
        new List<Pet>{ new Pet { Name="Barley", Age=8 },
                       new Pet { Name="Boots", Age=4 },
                       new Pet { Name="Whiskers", Age=1 } };

    foreach (Pet pet in pets1.DefaultIfEmpty(defaultPet))
    {
        Console.WriteLine("Name: {0}", pet.Name);
    }

    List<Pet> pets2 = new List<Pet>();

    foreach (Pet pet in pets2.DefaultIfEmpty(defaultPet))
    {
        Console.WriteLine("\nName: {0}", pet.Name);
    }
}

/*
This code produces the following output:

Name: Barley
Name: Boots
Name: Whiskers

Name: Default Pet
*/

LINQ Operators – Part VIII

2009-07-02T04:33:00.001+03:00

Aggregation operators

Aggregation operators are Average, Count, LongCount, Sum, Max, Min and Aggregate. Aggregation operators are available only as methods in C# (VB also have a comprehension syntax for aggregation operators).

Average: Gets the average of a numeric collection. T-SQL counterpart is Avg()

Sum: Sums the values in a numeric collection. T-SQL counterpart is Sum()

Min,Max: Gets minimum and largest values in a sequence. T-SQL counterparts are Min(), Max()

Count, LongCount: Counts the elements in a collection. T-SQL counterparts are Count(), Count_Big().

Aggregate: Performs custom aggregation on a set of values. T-SQL has no counterpart.

int[] scores = {60,75,89};
scores.Average().Dump("Average");
scores.Sum().Dump("Sum");
scores.Max().Dump("Max");
scores.Min().Dump("Min");
scores.Count().Dump("Count");
scores.LongCount().Dump("LongCount");

Count and LongCount have a filtering parameter that filters what to count based on a boolean expression as in this sample:

int[] numbers = {17,122,23,12,22};
numbers.Count( n => n%2 == 0).Dump("Even Numbers count");

Count and LongCount are same except that LongCount returns a long (64 bits integer).

All these methods (except Count and LongCount) have a "selector" parameter to specify "what" to sum,average,min or max. Here are some samples:

Customers.Select(
   c => new
   {
      customer = c.CompanyName,
      firstPurchase = c.Orders.Min( o => o.OrderDate ),
      lastPurchase = c.Orders.Max( o => o.OrderDate ),
      orders = c.Orders.Count,
      totalSale = c.Orders.Count == 0 ? 0 :
      c.Orders.Sum( o => o.OrderDetails
              .Sum( od => od.Quantity * od.UnitPrice ))
   } )
   .OrderBy( c => c.customer )

This sample uses Min, Max, Count, Sum to generate a summary statistics on customer purchases. Here is a sample for getting averages:

double quantity = OrderDetails.Average( od => od.Quantity );
decimal price = OrderDetails.Average( od => od.UnitPrice );
decimal sale = OrderDetails.Average( od => od.Quantity * od.UnitPrice );

Console.WriteLine("{0} {1} {2}", quantity, price, sale );

Aggregate is the interesting one here that is new to VFP developers (and SQL developers as well). Aggregate allows custom aggregations to be done and is supported only in local data (not supported by Linq To SQL). Here are some samples:

IEnumerable<int> numbers = Enumerable.Range(1,10);
int sum = numbers.Aggregate(
(current, next) => current + next );
int sumProduct = numbers.Aggregate(
(current, next) => current * next );

sum.Dump();
sumProduct.Dump();

First one is simply a custom implementation of Sum(). Second one gets a product of the numbers in list ( 1 * 2 * 3 * 4 … 10 ). Next example shows that to use aggregate it doesn't have to be a numeric.

Customers.Select(c => c.CustomerID).ToArray().
Aggregate( (current, next) => current + ", " +next )

This sample creates a comma separated list of all customer ID values. Note that we get the customer IDs to an array first to make the collection local.

Aggregate have two more signatures. Next one uses an initial "seed" value (in the above samples the seed value was the first element in the list). I think VS Linq samples have one of the best samples for this one:

        [Category("Aggregate Operators")]
        [Title("Aggregate - Seed")]
        [Description("This sample uses Aggregate to create a running account balance that " +
                     "subtracts each withdrawal from the initial balance of 100, as long as " +
                     "the balance never drops below 0.")]
        public void Linq93() {
            double startBalance = 100.0;

            int[] attemptedWithdrawals = { 20, 10, 40, 50, 10, 70, 30 };

            double endBalance =
                attemptedWithdrawals.Aggregate(startBalance,
                    (balance, nextWithdrawal) =>
                        ( (nextWithdrawal <= balance) ? (balance - nextWithdrawal) : balance ) );

            Console.WriteLine("Ending balance: {0}", endBalance);
        }

Next version of Aggregate allows you to have a custom selector projection on the result.

Products.Select( p => new { p.ProductName, p.Category.CategoryName }).ToArray()
.Aggregate(
       new {Beverages=0,Condiments=0,Other=0}, // initial seed value
       (acc,next) => next.CategoryName == "Beverages"
                     ? new {Beverages=acc.Beverages+1, Condiments=acc.Condiments, Other=acc.Other}
                     : ( next.CategoryName == "Condiments"
                     ? new {Beverages=acc.Beverages, Condiments=acc.Condiments+1, Other=acc.Other}
                     : new {Beverages=acc.Beverages, Condiments=acc.Condiments, Other=acc.Other+1} ),
       result => String.Format("Beverages: {0}, Condiments: {1}, Others:{2}", result.Beverages, result.Condiments, result.Other) // projection
)

It looks confusing and yes it is. Probably you wouldn't want to aggregate like this but use a for loop. I wrote this one just to show how you could initialize a complex (here an anonymous type with Beverages,Condiments and Other properties) seed value, do accumulation based on category names and finally project the result to a custom string.

LINQ Operators – Part VII

2009-06-25T02:31:00.001+03:00

Generation operators:

Range: Given a start and count generates a list of integers.

Enumerable.Range( 3, 10)

Generates integers 3,4 … 12.

Repeat: Is similar to replicate() –but not limited to strings- and generates a list of a given element (either a single element or not) repeating it N times.

Enumerable( 'A', 5).Dump(); // Char A 5 times
Enumrable.Repeat(1, 3).Dump(); // int 1 3 times
var o = Customers.Single( c => c.CustomerID == "ALFKI" ); // Single is a method specifying that the result is a single element
Enumerable.Repeat(o,10).Dump(); Customers o 10 Times

var customers = Customers.Where( c => c.Country == "USA" );
Enumerable.Repeat(customers, 3).Dump(); // a series of customers repeated 3 times

Note that Repeat() repeats the given source which is a singleton element or a collection.

Empty: Generates an "empty" sequence of a given type of source. Though it is "empty", it is still a representation of given type rather than NULL.

var customers = Enumerable.Empty<Customers>();
customers.Dump(); // nothing
customers.Count().Dump(); // 0

Quantifier Operators:

Contains: Returns true if a given sequence contains a given element. T-SQL counterpart is IN, Exists.

string[] countries = "USA,UK,Germany".Split(',');
Customers.Where( c => countries.Contains(c.Country)).Dump();

Selects customers where Customers.Country IN ("USA","UK","Germany").

Any: Returns true if a sequence have "any" elements. T-SQL counterpart is Exists, IN.

Customers.Where( c => c.Orders.Any() )

Returns all Customers that has at least one Order.

Customers.Where(c => c.Orders.Any( o => o.OrderDetails.Count() > 5))

Returns all customers who have an Order with at least 6 items. Here is the result set using Northwind (picture cut):

As you may have noticed Any() can do what Contains() do. For example we could rewrite Contains sample:

string[] countries = "USA,UK,Germany".Split(',');
Customers
.AsEnumerable()
.Where( c => countries.Any( cn => cn == c.Country )).Dump();

However in case of an IQueryable (like Linq To SQL or Entity Framework) Contains() is supported to have a local sequence while Any() is not supported - and its syntax is easier for this sample.

All: All is like Any() but always have a filter condition where all elements of a sequence should match for All() to return true.

Orders
.Where( o => o.OrderDetails
.All( od => od.Product.Category.CategoryName == "Beverages" ))

Selects all the orders where all order items are from "Beverages" category. You can take a look what those order ID, product name and category names are using the methods we learned earlier:

Orders
.Where( o => o.OrderDetails
.All( od => od.Product.Category.CategoryName == "Beverages" ))
.SelectMany(
    o => o.OrderDetails,
    (o, od) => new
   {
       OrderID = o.OrderID,
       Product = od.Product.ProductName,
       Category = od.Product.Category.CategoryName
   })
.OrderBy( o => o.OrderID )

LINQ Query Operators – Part VI

2009-06-22T01:05:00.001+03:00

Set operators

In set operators we will discuss Distinct(), Union(), Intersect(), Except() and Concat().

Distinct: Removes the duplications and selects unique items. T-SQL counterpart is "Distinct".

Union: Returns the union of items from multiple sets. In other words adds one set's elements to other removing duplicates. T-SQL counterpart is "Union".

Concat: Returns the union of two sets without dropping the duplicates. T-SQL counterpart is "Union All".

Intersect: Returns elements from two sets which exists in both sets. T-SQL counterpart "exists", "In (….)" (SQL2008 "intersect").

Except: Returns elements from a set which doesn't exist in other set. T-SQL counterpart "not exists", "not In (….)" (SQL2008 "except").

Customers.Select(c=>c.Country).Distinct()

is the same as:

Select DISTINCT Country FROM Customers

If we have used:

Customers.Distinct()

The SQL would be:

Select * from Customers

Just as we expected. (The actual generated SQL lists the fields instead of using *)

Distinct accepts a parameter to define an "equality" interface (IEqualityComparer<TSource>). So you can for example accept the items that have say "Netherlands" and "Holland" as same while applying a distinct operation. This IEqualityComparer interface is not only used by Distinct but by a series of methods like Group, GroupJoin, Intersect, Except, Union. Before we go into sampling IEqualityComparer lets make a note that objects are compared using equality comparison and hashcode. Hashcode is a "signature" of object. Two different objects have different hashcode even if their properties are same. It is not hard to understand this. In VFP consider:

use myTable
scatter name o1
skip
scatter name o2

First two records might exactly have the same data in them and hence o1, o2 would match in their properties but still they are 2 different objects as shown in the sample code below:

Create Cursor sampledata ( firstName c(10), lastname c(10))
Insert Into sampledata Values ("Joe","Doe")
Insert Into sampledata Values ("Joe","Doe")
Insert Into sampledata Values ("Frank","Doe")

Locate
Scatter Name o1
Skip
Scatter Name o2

Clear
? o1.firstName,o1.lastname
? o2.firstName,o2.lastname

? "FirstNames are equal",o1.firstName == o2.firstName
? "LastNames are equal", o1.lastname == o2.lastname
? "Objects have identical properties", Compobj(o1,o2)
? "Objects are same", o1 = o2

Lets create some SQL server data to see all these visually:

create database LinqTest
go

use LinqTest
go

create table badData (
personId int identity primary key,
firstName varchar(15), lastName varchar(20),
email1 varchar(50), email2 varchar(50),
phone1 varchar(15), phone2 varchar(15), phone3 varchar(15))

insert into badData
(firstName,lastName, email1,email2, phone1,phone2,phone3)
values
('John', 'Doe', 'jdoe@myhost1.com','jdoe@myhost2.com','111-111 11 11', '111-111 11 12', '111-111 11 13')

insert into badData
(firstName,lastName, email1,email2,phone1,phone2,phone3)
values
('John', 'Doe', 'jdoe@myhost1.com','jdoe@myhost2.com','111-111 11 11', '111-111 11 12', '111-111 11 13')

insert into badData
(firstName,lastName, email1,email2,phone1,phone2,phone3)
values
('John', 'Doe', 'jdoe@myhost2.com','jdoe@myhost1.com','111-111 11 13', '111-111 11 11', '111-111 11 12')

insert into badData
(firstName,lastName, email1,email2,phone1,phone2,phone3)
values
('Joe', 'Doe','jdoe@myhost2.com','', '', '', '')

insert into badData
(firstName,lastName, email1,email2,phone1,phone2,phone3)
values
('Joseph', 'Doe', '','', '111-111 11 13', '', '')

insert into badData
(firstName,lastName, email1,email2,phone1,phone2,phone3)
values
('Frank', 'Doe', 'fdoe@myhost1.com','fdoe@myhost2.com','111-111 11 11', '', '')

insert into badData
(firstName,lastName, email1,email2,phone1,phone2,phone3)
values
('John', 'Doe','jdoe@myhost3.com','jdoe@myhost4.com', '222-111 11 11', '222-111 11 12', '222-111 11 13')
go

Select * from badData

Create the same data as badData2 – to use with Union and see the effect of hashcode. Now we will call Distinct() and Union() using this data. Before doing that lets check our data. It is a bad data design, but here did that on purpose. First two are plain duplicate entry. 3rd one is a duplication where data is in different columns (email1, email2 swapped, phone1,phone2,phone3 are scrambled). 4th, 5th and 6th have different names and only part of the data is entered but they match to others, which is likely to be another kind of duplication (or not a duplication but suppose we want to call offices and call each office only once, then first 6 are the same office). Last one is a match on name but neither his emails nor phones match others which we would think of as another person. Here is our code:

void Main()
{
   var b1 =
      BadData.Select( b =>
       new Person {
         FirstName = b.FirstName, LastName = b.LastName,
         Email1 = b.Email1, Email2 = b.Email2,
         Phone1 = b.Phone1, Phone2 = b.Phone2, Phone3 = b.Phone3
       });

   var b2 =
      BadData2.Select( b =>
       new Person {
         FirstName = b.FirstName, LastName = b.LastName,
         Email1 = b.Email1, Email2 = b.Email2,
         Phone1 = b.Phone1, Phone2 = b.Phone2, Phone3 = b.Phone3
       });

   b1.Union( b2 ).Dump("b1 union b2 - Union in SQL");
   b1.Distinct().Dump("b1 distinct - Distinct in SQL");

   PersonEqualityComparer cmp = new PersonEqualityComparer();
   b1.AsEnumerable().Union( b2.AsEnumerable(), cmp ).Dump("b1 union b2 with comparer");
   b1.AsEnumerable().Distinct( cmp ).Dump("b1 distinct with comparer");
}

// Define other methods and classes here
public class Person
{
public string FirstName {get;set;}
public string LastName {get;set;}
public string Email1 {get;set;}
public string Email2 {get;set;}
public string Phone1 {get;set;}
public string Phone2 {get;set;}
public string Phone3 {get;set;}
}

public class PersonEqualityComparer : IEqualityComparer<Person>
{
   public bool Equals(Person x, Person y) {
      return IsMatch(
         new string[]{x.Email1,x.Email2},
         new string[]{y.Email1,y.Email2}
         ) || IsMatch(
         new string[]{x.Phone1,x.Phone2,x.Phone3},
         new string[]{y.Phone1,y.Phone2,y.Phone3});
   }

   public int GetHashCode(Person obj) {
      return obj.GetHashCode();
   }

   private bool IsMatch(string[] words1,string[] words2) {
      return (
         words1.Where(w => !String.IsNullOrEmpty( w ))
         .Intersect(
         words2.Where(w => !String.IsNullOrEmpty( w ))
         ).Count() > 0);
   }
}

Here are the results:

It is what you expected for the first two results (distinct and union is done directly in T-SQL but next two looks surprising. If I didn't use a new Person class then those would be same as the first two results. But we created a custom comparer to mean that:

"If any email or phone matches then it is a duplicate"

so why would we get too many results? Much more than the one without a custom comparison? Well, we defined a custom Equals() method but we still return the Hash Code of the original object. Remember to be accepted same not only should Equals() return true but hash codes must match as well. For this reason none of them is equal and union returns a result like as if we wanted to do a "union all". To correct it we would just change the GetHashCode() implementation and see what we would get:

   public int GetHashCode(Person obj) {
      return "".GetHashCode();
   }

and here are our new result sets (last two displayed):

Now that we have covered defining our own equality operator, Union and Distinct lets check other methods Concat,Intersect and Except.

Customers
.Where( c => c.Country == "USA" )
.Select( c => new {c.CustomerID,c.CompanyName,c.Country} )
.Concat(
Customers
.Where( c => c.Country == "UK" )
.Select( c => new {c.CustomerID,c.CompanyName,c.Country} )
)

This sample is a typical "UNION ALL" of USA customers set and UK customers set ( Select( c => new … ) projection is for sampling only, if we didn't have Select() parts it would be all fields).

Products.Where( p => p.OrderDetails.Any( od => od.Order.Customer.Country == "Argentina" ))

Here we use Any() which we will see later in "Quantifier" operators. What it does is to return true if any item in the set has a match to its filter expression ( T-SQL exists or any). This query is selecting products that are bought by Argentina customers. Here is the result set (partial):

Products.Where( p => p.OrderDetails.Any( od => od.Order.Customer.Country == "Argentina" ))
.Intersect(
Products.Where( p => p.OrderDetails.Any( od => od.Order.Customer.Country == "Belgium" ))
)

In this query Second part is doing the same for Belgium customers creating a seconds set. Then using INTERSECT we are getting the Products which are common to both sets. Here is the result set (picture cut for space):

As you should notice there are 14 common items (Argentina customers bought 32 different products). Looking at the results we could say that products with Id 5, 7, 11, 13, 14, 16, 32,34 … are not bought by Belgium customers. Lets find out which products bought by Argentina customers were not on demand by Belgium customers (in other words: what are the products that are bought by Argentina customers EXCEPT those are also bought by Belgium customers).

Products.Where( p => p.OrderDetails
.Any( od => od.Order.Customer.Country == "Argentina" ))
.Except(
Products.Where( p => p.OrderDetails
.Any( od => od.Order.Customer.Country == "Belgium" ))
)

Here is the result set:

Like in Distinct and Union, Intersect, Except methods have support for additional custom comparer.

PS: Because of years of working with SQL I happened to write insert statements for LinqTest database in T-SQL. Instead I could do the data insertion part using Linq. For example if you wanted add few more it would look like:

void Main()
{
   List<BadData> persons =
     new List<BadData>
     {
       new BadData {FirstName="Frank",LastName="Smith",
           Email1="m1",Email2="m2",
           Phone1="p1",Phone2="p2",Phone3="p3"},
       new BadData {FirstName="Cetin",LastName="Basoz"},
       new BadData {FirstName="Fred",LastName="Flintstone",
           Email1="fred@yellowstone.com",Phone1="555-111 11 11"}
     };

   BadData.InsertAllOnSubmit(persons);
   BadData.Context.SubmitChanges();

   // Check results
   BadData.Dump();
}

Or you could insert a single element like this:

BadData b = new BadData();
b.FirstName = "Jack";
b.LastName = "Smith";
b.Phone2 = "666-123 45 67";

BadData.InsertOnSubmit( b );
// more code if any
BadData.Context.SubmitChanges();

See you on next post.

LINQ Operators – Part V

2009-06-05T03:23:00.001+03:00

Joining operators:

Join: If you remember from query methods join is not needed in the case of direct relations in the model (however local queries are faster with join). You can create inner and left joins using Join operator. Syntax is simple:

Customers.Join(            // Customers
   Orders,                     // inner join Orders
   c => c.CustomerID,    // (Customers as c) on c.CustomerID
   o => o.CustomerID,   // (Orders as o) = o.OrderID
   (c, o) => new            // local aliases – select c.CustomerID, o.OrderID, o.OrderDate
       {
            c.CustomerID,
            o.OrderID,
            o.OrderDate
       } )

Is equivalent to (and generates) this SQL:

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID

You can use a custom equality comparer delegate as a parameter.

GroupJoin: GroupJoin is a type of join that groups the results into lists based on join key – list of lists where inner lists are "grouped" on key.

Customers.GroupJoin(
   Orders,
   c => c.CustomerID,
   o => o.CustomerID,
   (input,outputSequence) => outputSequence
           .Select( r => new { r.CustomerID, r.OrderID, r.OrderDate } ) )

LINQ Operators – Part IV

2009-05-31T23:14:00.001+03:00

Grouping operators

GroupBy : GroupBy is used to group a sequence into subsets. At first look it feels like it is same as T-SQL "Group By" but it is not. It slices a sequence into multiple "rows" where each row have a "key" and subset of sequence matching to those key. Here is a screen shot how it looks with this simple query:

Customers.GroupBy( c => c.Country )

GroupBy has an easy syntax with some overloads. You can optionally specify an element selector (projection) and also optionally specify a custom equality comparer. Equality comparer is less used and not different from the sample we used in OrderBy().

Here is a sample with an element selector:

Customers
   .GroupBy (
      c => c.Country,
      c =>
         new
         {
            CustomerID = c.CustomerID,
            CompanyName = c.CompanyName
         }
   )

Latter GroupBy selects only two properties of Customers instead of all properties. You can also group by multiple keys:

Customers
   .GroupBy (
      c =>
         new
         {
            Country = c.Country,
            Region = c.Region
         }, // keySelector
      c =>
         new
         {
            CustomerID = c.CustomerID,
            CompanyName = c.CompanyName
         } //elementSelector
   )

In all these queries you are getting a sequence of "groupings". You can "browse" the result with nested foreach() which is sort of nested scan…endscan:

Partial result:

In other words we are dealing with a sequence of sequences. With LinqToSQL inner sequences are queried as they are needed resulting in "groups count" + 1 selects send to backend. This may or may not be the behavior you want depending on where you need it (you can see the series of SQLs in LinqPad's SQL tab). To prevent this behavior you can convert Customers to an IEnumerable from IQueryable (or to list, array…) first and then do grouping. For example:

Customers.AsEnumerable().GroupBy( c => c.Country )

AsEnumerable() is a conversion operator that converts the IQueryable to an IEnumerable ( in other words from a remote source to local source – similar to cursors ). We will use AsEnumerable() in some of the following samples that show statistics, nested grouping, distinct sequences.

This sample shows some statistics about Customers grouped by country:

Orders.GroupBy (
o => o.OrderDate.GetValueOrDefault().Year )
.Select (
   sales =>
   new
   {
     Year = sales.Key,
     Customers = sales
       .Select( s => s.CustomerID )
       .Distinct()
       .Count(),
     Countries = sales
       .Select( s => s.ShipCountry )
       .Distinct()
       .Count(),
     FirstSale = sales.Min( s => s.OrderDate ),
     LastSale = sales.Max( s => s.OrderDate )
   }
).OrderBy( s => s.Year )

This sample groups customerId and countries by years they made an "order":

Orders.AsEnumerable()
.GroupBy( o => o.OrderDate.GetValueOrDefault().Year )
.Select (
      sales =>
      new
      {
        Year = sales.Key,
        Customers = sales
            .Select( s => s.CustomerID )
            .Distinct(),
        Countries = sales
            .Select( s => s.ShipCountry )
            .Distinct()
       }
   )

This sample is a multilevel grouping. It first groups customers by first letter of countries and then by countries:

Customers.AsEnumerable()
.GroupBy( c => c.Country [0] )
.Select( c =>
      new
      {
        c.Key,
        countries =
           c.GroupBy( cn => cn.Country )
            .Select( cs =>
                 new
                 {
                   cs.Key,
                   clist =
                      cs.Select( cf =>
                        new
                        {
                           cf.CompanyName,
                           cf.Country,
                           cf.Region})
                        }).OrderBy( cn => cn.Key )
         }).OrderBy( c => c.Key )

There is another grouping operator called ToLookup() that creates one to many dictionary. I reserved it into another category.

LINQ Query Operators – Part III

2009-05-28T02:55:00.001+03:00

Ordering Operators:

Ordering operators like T-SQL "Order By" is used for ordering sequences in different ways. There are 5 ordering operators:

OrderBy
OrderByDescending
ThenBy
ThenByDescending
Reverse

Except the last operator Reverse() all those are used as:

operator( keySelector ) or operator( keySelector, customComparer ) - customComparer is for custom sorting and not supported by L2S (Reverse() is not supported either).

You should have noticed that "descending" is not a parameter but separate methods exist to sort descending.

OrderBy: Orders the sequence by a given "keySelector". i.e:

Linq	T-SQL
Customers.OrderBy( c => c.ContactName )	Select * from Customers order by ContactName

Seconds overload accepts a comparer. Here is a sample that sorts orders by :

// In LinqPad select C# Program from language combo
// Delete default code
// Copy Paste or better hands on, write yourself
// and press F5 to run
void Main()
{
// This overload is not supported by L2S
// Getting into a local list first
// We would review later what does "local" mean
// for now just consider queries on ordList
// is not Linq To SQL
// (sort of you are querying a local cursor which
// you created by a remote call to SQL server)
var ordList = Orders.Select( o =>
                  new {
                           OrderId = o.OrderID,
                           OrderDate = o.OrderDate
                         }).ToList();

// creating an instance of custom comparer
// and passing it as a parameter
// final Select projection is about showing
// the result of sort
// Note that the sort is by "Day Of Week" enumeration
// and not by "WeekDay" alphabetically
var myCustomList =
               ordList
                  .OrderBy( o =>
                                 o.OrderDate,
                                 new OrderComparer() )
                 .Select( o => new {
                            o.OrderId,
                            o.OrderDate,
                            o.OrderDate.GetValueOrDefault().DayOfWeek} );

myCustomList.Dump();
}

// Here is our custom sorting class
// It accepts parameters of type nullable DateTime
// and returns comparison result based on
// DayOfWeek – like DOW() in VFP

public class OrderComparer : IComparer<DateTime?>
{
   public int Compare(DateTime? x, DateTime? y)
   {
     return
        x.GetValueOrDefault().DayOfWeek
         .CompareTo(
        y.GetValueOrDefault().DayOfWeek );
    }
}

OrderByDescending: Same as OrderBy() except that it sorts in descending order (it was obvious,isn't it).

ThenBy, ThnenByDescending: ThenBy and ThenByDescending are used to sort on multiple columns. Hmm why would you need something like this? Can't you simply use chain of OrderBy(), OrderByDescending() operators? Of course you can but I think these were added to prevent you from writing it the way you intended (like in natural English you would be saying order by X then by Y then by Z). Probably this is best explained by a sample. We want to sort customers by their country and then by company name. It is very easy to write it as in first query and fail to get what we really intended.

Linq	Corresponding SQL
Customers .OrderBy(c => c.Country) .OrderBy(c => c.CompanyName) .Select( c => new {c.Country, c.CompanyName} )	Select [t0].[Country], [t0].[CompanyName] FROM [Customers] As [t0] ORDER BY [t0].[CompanyName], [t0].[Country]
Customers .OrderBy(c => c.CompanyName) .OrderBy(c => c.Country) .Select( c => new {c.Country, c.CompanyName} )	SELECT [t0].[Country], [t0].[CompanyName] FROM [Customers] AS [t0] ORDER BY [t0].[Country], [t0].[CompanyName]
Customers .OrderBy( c => c.Country) .ThenBy ( c => c.CompanyName) .Select( c => new {c.Country, c.CompanyName} )	SELECT [t0].[Country], [t0].[CompanyName] FROM [Customers] AS [t0] ORDER BY [t0].[Country], [t0].[CompanyName]

In SQL the order of columns dictate it and it is easy to write exactly what we want ( order by country and then by company ). However in Linq method syntax, keep in mind with each and every chained method we get a new intermediate sequence as a result ( and you could see how a series of methods is working by attaching a "Dump()" to each of those methods ). i.e:

Customers
.Take(10).Dump("Got top 10")
.Select( c => new {c.Country, c.CompanyName} ).Dump("Projected to 2 columns: Country,Company")
.OrderBy( c => c.Country).Dump("Sorted on Country")
.OrderBy( c => c.CompanyName).Dump("Sorted on Company")

As you can see we are first sorting on country and "then by" without a ThenBy() operator on company. The net effect we are sorting on company name only. ThenBy() operator excellently translates our intention about "by Country THEN BY Company Name".

Reverse: On a local sequence reverses the sequence as its name suggest. i.e:

int[] numbers = {1,2,3,4,5};
numbers.Reverse().Dump();

string reversed = new string(
"Reverse Me".Reverse().ToArray()
);

Console.WriteLine(reversed);

LINQ Query Operators – Part II

2009-05-27T02:26:00.001+03:00

Projection Operators:

Select: Selects items from a source, projecting the results as necessary.

When the input sequence would be selected as is without any projection ( aka * in T-SQL ) using Select() is not necessary and looks a little ugly. i.e.:

Customers.Select( c => c )

Instead we would simply write just "Customers" which is the enumerable itself. In query syntax it is the same as writing:

from c in Customers select c

Here are some Select projections and its Query expression, T-SQL counterparts:

T-SQL	Select CompanyName from customers
Query Syntax	from c in Customers select c.CompanyName
Method Syntax	Customers.Select( c => c.CompanyName )

T-SQL	Select firstName, lastName, firstName + ' ' + lastName As fullName from employees
Query Syntax	from e in Employees select new { e.FirstName, e.LastName, fullName = e.FirstName + " " + e.LastName }
Method Syntax	Employees .Select( e => new { e.FirstName, e.LastName, fullName = e.FirstName + " " + e.LastName } )

There is another overload of Select() which accepts an index parameter and is not supported by Linq To SQL and EF. Here is a simple example that lists the DBF files from VFP samples data folder enumerating them with their position (reminder: in C# indexing, arrays start from 0):

Directory.GetFiles(
   @"c:\Program Files\Microsoft Visual Foxpro 9\Samples\Data", "*.dbf")
   .Select( (filename,position) =>
        new {fileNo = position+1,filename} )

In the samples above, with the "new" keyword the type created is an anonymous type. Sometimes we would want to create a type of our own (similar to "into cursor cursorName" – cursorName might a different structure than the source). To do that we could create a new class and qualify the "new" type with that class. i.e.:

void Main()
{
var q = from c in Customers
          select new
          myCustomer {
            ID = c.CustomerID,
            company = c.CompanyName,
            orderCount = c.Orders.Count
          };

q.Dump();
}

public class myCustomer
{
public string ID {get;set;}
public string company {get;set;}
public int orderCount {get;set;}
}

This produces the SQL:

SELECT
[t0].[CustomerID] AS [ID],
[t0].[CompanyName] AS [company],
(
    SELECT COUNT(*)
    FROM [Orders] AS [t1]
    WHERE [t1].[CustomerID] = [t0].[CustomerID]
) AS [orderCount]
FROM [Customers] AS [t0]

SelectMany: The Select() method works as Select( input =>output ) where it produces one output per input. Consider a query getting customers and orders of customers (maybe customerID and CompanyName from Customers and OrderID, OrderDate from orders to show simpler output):

Select
[t0].[CustomerID], [t0].[CompanyName], [t1].[OrderID] As [OrderID], [t1].[OrderDate]
FROM [Customers] As [t0], [Orders] As [t1]
WHERE [t1].[CustomerID] = [t0].[CustomerID]

This trivial SQL generates a 2D flat table. If we attempt to create this with a Select() we could do that using an expression like below:

Orders.Select( o =>
   new
   {
     o.Customer.CustomerID,
     o.Customer.CompanyName,
     o.OrderID,
     o.OrderDate
   } )

Which produce this SQL:

Select
[t1].[CustomerID], [t1].[CompanyName],
[t0].[OrderID], [t0].[OrderDate]
FROM [Orders] As [t0]
LEFT OUTER JOIN [Customers] As [t1]
On [t1].[CustomerID] = [t0].[CustomerID]

and the flat 2D result that we are accustomed to. If you look at the SQL closely you would notice that it is sort of cheat creating an inner join (Orders driving the results and already each Order has a "parent" Customer, this in effect is an inner join). We could also look at it as:

use Customers order tag CustomerID

select 0

use Orders

set relation to CustomerID into Customers

The old Indian trick that we used in reports in old days to get all orders of customers using relations. If we did the relation from Customers into Orders and then browse each table side by side remember per Customer in left browse we would see "Many" Orders on right. Or better yet we all wanted to have Grid in Grid or hierarchical grids showing one Customer in a row and her Orders in a grid in another grid cell. Doing that is not trivial except using relations and that would be a single "thing". Now that we are in objects world a single object would be representing a data structure where per customer "row" there are multiple order "rows" (think of a report where data is grouped on CustomerID). If we have started from Customers (and remember we get one output per input) we would end up with such an "object" which is an ordered enumerable of Customers along with its Orders. Here is our new query:

Customers.Select( c =>
   new
   {
     c.CustomerID,
     c.CompanyName,
     OrderList = c.Orders.Select( o => new {o.OrderID, o.OrderDate})
   } )

Producing SQL:

Select
[t0].[CustomerID],
[t0].[CompanyName],
[t1].[OrderID], [t1].[OrderDate],
(
    Select COUNT(*)
    FROM [Orders] As [t2]
    WHERE [t2].[CustomerID] = [t0].[CustomerID]
) As [value]
FROM [Customers] As [t0]
LEFT OUTER JOIN [Orders] As [t1]
On [t1].[CustomerID] = [t0].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

If we run this SQL we would get a flat list as always. However the Linq query returns an object result which is more like "parent-child" packed data (like a 3D table). Here is part of output from LinqPad to understand better (and I hope you are following these testing in LinqPad yourself):

This is more like grouping that we may like. We may want this as a flat 2D as we are accustomed to and SelectMany() comes into play there. With query syntax it is as easy as:

from c in Customers
from o in c.Orders
select new
   {
      c.CustomerID,
      c.CompanyName,
      o.OrderID,
      o.OrderDate
   }

this is converted to SelectMany() as shown here:

Customers
   .SelectMany (
      c => c.Orders,
      (c, o) =>
         new
         {
            CustomerID = c.CustomerID,
            CompanyName = c.CompanyName,
            OrderID = o.OrderID,
            OrderDate = o.OrderDate
         }
   )

This concludes projection operators subject.

Note: I intentionally didn't mention some details about operators shown and left out some operator(s) that may be part of categories we talked about in order to prevent confusion. Also note that these are standard operators. Linq is evolving and already there are many more operators than you would see in standard operators set. Trying to keep it simple and summarized I might be skipping details more than I should, I hope not.

LINQ Query Operators – Part I

2009-05-24T05:20:00.001+03:00

Orders.OrderBy(o => o.OrderDate).Skip(5)

Before checking the operators lets remind some points.

Query operators are extension methods

Some Linq operations are only available with operators (or to say it otherwise "method syntax")

Syntax includes the source itself which need not be specified (because implemented as static extension method on IEnumerable, IOW source). Typical syntax looks like:

public static IEnumerable<TSource> Where<TSource>( this IEnumerable<TSource> source, Func<TSource, Boolean> predicate)

Looks like Chinese? It is very easy once you understand indeed. You can think of it as:

IEnumerable<TSource> : It returns something that can be enumerated ( like an array, list, records …) - and its type is symbolically TSource.

Where<TSource>( this IEnumerable<TSource> source : First parameter is also something that can be enumerated and since it is an extension method of IEnumerable itself we don't need to feed source parameter.

Up to this point it is simple, gets something like a list and returns back another list. Just like an SQL select does.

Func<TSource, Boolean> predicate) : Here comes the interesting part. It accepts a "function" that has the TSource type input parameter (you can think of TSource as a row of customer when the source is customers table) and returns back true or false. If true is returned "record" is selected.

Now lets check this "Where" using in a Linq query and compare to its counterpart SQL select and Linq query syntax:

SQL select:

select * from Customers where Customers.Country = "USA" into cursor usaCustomers

LINQ Query Syntax:

IEnumerable<Customers> usaCustomers = from c in Customers where c.Country == "USA" select c;

LINQ Method Syntax:

IEnumerable<Customers> usaCustomers = Customers.Where( c => c.Country == "USA" );

Lets dissect the method syntax and see different ways of writing the same thing:

Customers.Where( c => c.Country == "USA" );

TSource is Customers. In method Where(), c is the input parameter to function (remember it represents something like a Customers row, so it has columns as properties). it returns the result of check: c.Country == "USA" which is a true or false. We could write the same thing like this:

Customers.Where( ( c ) => c.Country == "USA" );

When parameter count is one, we wouldn't want to do that however and simply use the previous syntax. When parameter count is multiple we put them within parentheses.

For readability we are allowed to write the exact same thing like this (the newline and extra whitespace between Customers and .Where is insignificant):

IEnumerable<Customers> usaCustomers = Customers

                                      .Where( c => c.Country == "USA" );

Now that I think we are armed with basic syntax we can move on. LINQ operators are divided into categories. Members of categories may be different from source to source but in general all sources have same categories (and some operators are listed in more than one category).

Filtering and Partitioning Operators:

Where: This is the filtering operator which any VFP developer would be most familiar with. It has two forms

Typical function getting a source and returning a boolean (filters out for the ones returning false)
```
var usaCustomers = Customers.Where( c => c.Country == "USA" );
```

A function getting an index parameter besides source (this one is not supported with Linq To SQL or Linq to Entity Framework because there isn't a supporting SQL for this. Practically speaking methods that do not a SQL counterpart do not work with L2S).
```
int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
```
```
 
```
```
var some = numbers
```
```
    .Where((c, index) => ( 
```
```
                         (c%2 == 0) &&  
```
```
                         (index >= 8) && (index <= 15)));
```
```
 
```
```
some.Dump("Even numbers between positions 8-15");
```

1st syntax simply select customers from USA. 2nd syntax limits the "records" to position (sort of recno()) to 8-15 and within that range select numbers that are even.

Skip: Skips N elements.

int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};

var some = numbers.Skip(5);

some.Dump("Skipped first 5 numbers");

In T-SQL it looks like:

select number from

(select number,Row_Number() over (order by number) as row from numbers) numberList

where row > 5

Actually if you run this against Northwind:

Orders.OrderBy(o => o.OrderDate).Skip(5)

This is the generated T-SQL code:

-- Region Parameters

DECLARE @p0 Int SET @p0 = 5

-- EndRegion

SELECT [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID],

   [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate],

   [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress],

   [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry]

FROM (

    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[OrderDate]) AS [ROW_NUMBER],

        [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID],

        [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate],

        [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress],

        [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]

    FROM [Orders] AS [t0]

    ) AS [t1]

WHERE [t1].[ROW_NUMBER] > @p0

ORDER BY [t1].[ROW_NUMBER]

You can also achieve the same result with a query like:

SELECT * from Orders

WHERE OrderId not in

   (select top 5 OrderID from orders ORDER BY OrderDate)

Take: Takes top N – IOW typical TOP N query operator.

Orders.OrderBy(o => o.OrderDate).Take(5)

T-SQL counterpart:

SELECT TOP 5 * FROM [Orders] order by OrderDate

Both Skip and Take have another version which is not supported by L2S:

SkipWhile: Skips over elements as long as a condition is true. It has 2 overloads:

First overload skips while a condition is true.
```
int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
```
```
var some = numbers.SkipWhile( n => n < 5 );
```
```
some.Dump("Skipped while number is less than 5");
```

Second overload skips while a condition is true and also gets an index parameter as input.
```
int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
```
```
var some = numbers.SkipWhile( (n, index) => index < 5);
```
```
some.Dump("Skipped while index position is less than 5");
```

TakeWhile: Takes elements as long as the condition is true. Similar to SkipWhile it has same 2 overloads.

int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};

var some = numbers.TakeWhile( n => n < 5);

some.Dump("Take while number is less than 5");

int[] numbers = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};

var some = numbers.TakeWhile( (n, index) => index < 5);

some.Dump("Take while index position is less than 5");

You can combine or in other words chain these operators. Here is a sample (and already we did one before using OrderBy() above):

Orders

 .OrderBy( o => o.CustomerID)

 .Where( o => o.Customer.Country == "USA" )

 .Skip(5)

 .Take(3)

Note that using Skip() and Take() combination you can easily create a "paging query" for a web application. For example this gets the records 21-30 (Page 3 where pages are 10 records high) in a particular order of a paged query:

Customers.OrderBy(c => c.CustomerID).Skip( 2*10 ).Take(10)

A few Linq resources

2009-04-23T03:54:00.002+03:00

It has been a long time since I posted my last entry. Sorry about that. I still don't have time for long posts but I thought I could at least provide some pointers to resources, samples, tools and such.

Here is a new book that is published recently:

Essential LINQ
by Charlie Calvert and Dinesh Kulkarni
Publisher: Addison-Wesley Professional
Pub Date: March 12, 2009
Print ISBN-10: 0-321-56416-2
Print ISBN-13: 978-0-321-56416-0
Web ISBN-10: 0-321-60475-X
Web ISBN-13: 978-0-321-60475-0
Pages: 600

I haven't read this book myself and learned about it today just by accident while testing a Linq sample delivered by Microsoft:)

Be sure to check samples in LinqPad and if you have Visual Studio 2008 check the samples delivered with VS located at:

C:\Program Files\Microsoft Visual Studio 9.0\Samples\1033\CSharpSamples\LinqSamples

Samples do not exist in Express version as far as I know but you could get the online version which is updated and maybe more recent then the one you have from:

http://code.msdn.microsoft.com/csharpsamples

Some more Linq flavors (not released yet – alpha,beta):

Parallel Linq
Linq to SharePoint
Linq to Active Directory

And finally I want to include a short reply to a question asked by a friend:

Question: Is Linq about querying data or can we use it to update the data too?

Answer: As I remember I told this before, Linq might be a misnomer and it is also used for updating the data where applicable. How we do it depends on which flavor we are using, but the key point is that while doing that we use an "object oriented" approach.

Query keywords – join, let

2009-04-01T04:44:00.001+03:00

join: Similar to SQL join, the join keyword is used to join sets. One thing you should note that, if there is a direct relationship in the object model a join is not needed. This behavior differs from SQL. Consider the infamous Northwind database. In SQL server (and in VFP northwind.dbc sample database) there are relations between the tables defined. You can't simply create joins without specifying a relation in your SQL:

select * from Customers, Orders

would be a newbie's error (most of the time) in an attempt to join two tables and expect them to be joined using CustomerId. End result would be a Cartesian join matching each Customer with every Order in Orders set. With Linq you can make the same mistake but it is harder to do:

from c in Customers
from o in Orders
select new { c.CustomerID, o.OrderID }

With the above SQL our intention would likely be an equijoin:

select Customers.CustomerID, Orders.OrderID from Customers, Orders where Customers.CustomerID = Orders.CustomerID

select Customers.CustomerID, Orders.OrderID from Customers inner join Orders on Customers.CustomerID = Orders.CustomerID

select Customers.CustomerID, Orders.OrderID from Customers left join Orders on Customers.CustomerID = Orders.CustomerID

First two are typical inner join queries and 3rd left outer join. In Linq writing such queries is easy. You could write the inner join like this one:

from c in Customers
from o in c.Orders
select new { c.CustomerID, o.OrderID }

and left outer join like this one:

from c in Customers
select new { c.CustomerID, OrderID = c.Orders.Select( o => o.OrderID ) }

While you could use "join" in these, join in Linq is for creating joins on properties that doesn't already define a relationship in object model (it is easy to forget we are doing a query against objects and object models). Within a database it is unlikely to make join queries that are not already defined with relationships but you may never know what you need. And you wouldn't always use Linq against collections that come from database based or some other type of well defined object model. We will however see "join" here with already well defined relationships just for the sake of your familiarity with those table and key field names. It makes it easier to guess the result without actually seeing it then.

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID
select new {c.CustomerID, o.OrderID }

I should also note that using join on primary – foreign key properties causes an "inner join" to be generated. There is also another type of join called "Group Join". You can create group join using "join" and "into" clauses (keywords) together:

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID into orderGroup
select new {c.CustomerID, orderGroup }

Or if we remember that we are working with objects could write it like this as well:

from c in Customers
join o in Orders on c equals o.Customer into orderGroup
select new {c.CustomerID, orderGroup }

But also there is one another and maybe easier to write and understand variation of the same query (and both generate the same left outer join) – provided there is a relationship that we can use in object model (you may call it "relationship" or "navigational property"):

from c in Customers select new {c.CustomerID, c.Orders}

let: Let is a cool keyword that allows you to store inline expressions. Those who use SQL server 2005 and later know that how much CTE (Common Table Expression - with {…} as ) is welcomed easing the writing of queries and increasing the performance as well. Let keyword is not CTE of course but looks very similar. Using it you assign a variable a complete expression inline creating a "sub-expression". That sub-expression is evaluated only once and can be used in operations within the outer expression. Now let's see how let lets you < g > write more succinct expressions. I will start with an exact copy from LinqPad's 5 minutes samples:

from p in Products
let spanishOrders = p.OrderDetails.Where (o => o.Order.ShipCountry == "Spain")
where spanishOrders.Any()
orderby p.ProductName
select new
{
    p.ProductName,
    p.Category.CategoryName,
    Orders = spanishOrders.Count(),
    TotalValue = spanishOrders.Sum (o => o.UnitPrice * o.Quantity)
}

Here spanishOrders is an inline variable (well not very true to call it variable, purists should forgive me) whose value is assigned from

p.OrderDetails.Where (o => o.Order.ShipCountry == "Spain")

expression. This expression is the same as this one written in comprehension syntax:

from o in p.OrderDetails where o.Order.ShipCountry == "Spain" select o

(and you could replace it with this one enclosing it in parentheses). It is later used in 3 places:

where spanishOrders.Any()
orderby p.ProductName
select new
{
    p.ProductName,
    p.Category.CategoryName,
    Orders = spanishOrders.Count(),
    TotalValue = spanishOrders.Sum (o => o.UnitPrice * o.Quantity)
}

I think you can see it without any further explanation. It is acting like an extraordinary SQL subquery (but it doesn't need to be something like a subquery, any expression would do and if that expression is returning something queryable you can query it). Here is another sample (this sample is from .Net documentation, you can see the original here):

string[] strings =
        {
            "A penny saved is a penny earned.",
            "The early bird catches the worm.",
            "The pen is mightier than the sword."
        };

// Split the sentence into an array of words
// and select those whose first letter is a vowel.
var earlyBirdQuery =
    from sentence in strings
    let words = sentence.Split(' ')
    from word in words
    let w = word.ToLower()
    where w[0] == 'a' || w[0] == 'e'
        || w[0] == 'i' || w[0] == 'o'
        || w[0] == 'u'
    select word;

earlyBirdQuery.Dump();

There are two let keywords here. First one gets each sentence and stores the result of Split(' ') function to words. "words" is another enumerable ( words of sentences in string ) and it is followed by another query ( from word in words ). For each word second "let" stores the result of word.Lower() to "w". "where" clause use that w multiple times. Without "let" word.Lower() itself would be called multiple times ( where w[0] == 'a' || w[0] == 'e' … w[0] == 'u' ).

Here is another sample which I wrote as a reply on MSDN C# forum. The question was (as I remember it):

There is tickdata like this one:

create table tickdata (timed datetime, price money, quantity int)
insert into tickData values ('06:01',100,20)
insert into tickData values ('06:02',90,30)
insert into tickData values ('06:03',129,10)
insert into tickData values ('06:11',112,8)
insert into tickData values ('06:12',150,60)
insert into tickData values ('06:20',110,10)
insert into tickData values ('06:23',120,5)
insert into tickData values ('06:24:59',130,15)
insert into tickData values ('06:25',140,35)

From this data generate stock trade like data that looks like:

period	open	low	high	close	volume
06:00 - 06:05	100.0000	90.0000	129.0000	129.0000	60
06:10 - 06:15	112.0000	112.0000	150.0000	150.0000	68
06:20 - 06:25	110.0000	110.0000	130.0000	130.0000	30
06:25 - 06:30	140.0000	140.0000	140.0000	140.0000	35

where period is defined as 5 minute intervals starting from the first available tickdata time and shows for each period with data. This was the Linq solution I came up with (besides "let" keyword there is grouping and query methods):

from myData in Tickdata.OrderBy( t => t.Timed ).AsEnumerable()
group myData by ((int)myData.Timed.Value.TimeOfDay.TotalMinutes) / 5 into barData
let bStart = TimeSpan.FromMinutes( barData.Key * 5 ).ToString()
let bEnd   = TimeSpan.FromMinutes( (barData.Key+1) * 5 ).ToString()
select new
{ period = bStart.Substring(0,5) + " - " + bEnd.Substring(0,5),
    open   = barData.FirstOrDefault().Price,
    low    = barData.Min( bd => bd.Price ),
    high   = barData.Max( bd => bd.Price ),
    close = barData.LastOrDefault().Price,
    volume = barData.Sum( bd => bd.Quantity ) }

This concludes our query keywords series. Other keywords are conceptual ones used to complete syntax (in, on, equals, by, ascending, descending).

Query keywords – orderby

2009-03-23T01:33:00.001+02:00

orderby: Is used for sorting the records. In comprehension syntax it is just like we use it in SQL. i.e:

from c in Customers
orderby c.Country, c.CompanyName descending
select c

Here we are ordering by Country (default ascending) and then by CompanyName in descending order.

In method syntax there are 4 methods:

OrderBy()
OrderByDescending()
ThenBy()
ThenByDescending()

Using method syntax above sample would be written as:

Customers
.OrderBy( c => c.Country )
.ThenByDescending( c => c.CompanyName )
.Select( c => c)

All 4 methods support a custom comparison method as a second parameter (for example you could create a method that instead of widely known alphabetical sort, sorts the strings based on a custom ordering – "10" comes after "2").

Here is a sample modified from LinqPad's OrderBy sample:

string[] strings = { "Tom", "Çetin",
   "I come before i", "Can", "in",
   "Dick", "Harry", "Mary", "Jay",
   "ş is between s and t", "smith"};

strings.OrderBy (n => n, StringComparer.Create( new System.Globalization.CultureInfo("tr-TR"), true))
.Dump ("Case insensitive ordering based on Turkish alphabet");

This second parameter is not supported in Linq to SQL.

Query keywords – group and into

2009-03-17T01:57:00.001+02:00

group (… by ): Is used for grouping the results based on a key. The key can be a plain or composite key. In Linq, the result of grouping is a List of Lists. You can think of it as data grouping in a report rather than a flat rows*columns representation of a table. The result is ( A Key per group, Matching Items per key ) collection – officially IGrouping ( TKey, TElement ). Another feature of group is that it is a keyword that can be used to end a query ( a query should end either with a select or group ).

A simple grouping would be to group Northwind Customers by their country values:

from c in Customers group c by c.Country

You can "group", "select" or "join" into an intermediary result using the keyword "into" and continue the query on that 'sub query'. It is like doing a sub query in SQL. "Key" is a property that is generated automatically and holds the group's key. i.e.:

from c in Customers
                group c by c.Country into countries
                select new
                {
                    Country = countries.Key,
                    Customers = from cus in countries
                                orderby cus.City, cus.CustomerID
                                select new { cus.CustomerID, cus.City }
                }

Here what we do is first we group the Customers by their country and 'alias' that 'sub query' as "countries". From that point on identifier "c" is not available to us (it is similar to SQL in that matter too). We continue with "countries" which is now a ( Country as key, Matching Rows ) collection.

You can also group a group but IMHO it is a hard to understand syntax:

from c in Customers.Select( c => new {c.CustomerID, c.Region, c.City, c.Country} )
   group c by c.Country[0] into g1
   from cc in ( from c in g1 group c by c.Country )
   group cc by g1.Key

Query keywords - select

2009-03-11T04:48:00.001+02:00

Before starting "select" I would like to list where to find LinqPad, SQL express, sample Northwind database, VC# express. To follow you need LinqPad (VS or VC# express IDE would do too, but I repeat LinqPad is a cool utility that worth the 3Mb download).

Linqpad download: http://www.LinqPad.Net

SQL express version download: http://www.microsoft.com/express/sql/default.aspx (you may also download cool SQL CE for some samples in the future – or to use from VFP)

VC# (or any one of the express): http://www.microsoft.com/express/product/ (If you check specific product pages there are tons of extras but don't get lost in them:)

Northwind database: http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

after downloading run SQL2000SampleDb.msi. It creates a new folder named "SQL Server 2000 Sample Databases", in it you would find both the sql scripts and database files (Northwind and Pubs). Either attach the database or run the sql script. i.e: In command prompt you can go to that folder and execute:

sqlcmd -S .\sqlexpress -i instnwnd.sql –E

( –S stands for SQL server instance which by default is .\SQLExpress for express edition, –i is input script file, and –E parameter tells that we are using windows authentication).

(you can also try this: http://msdn.microsoft.com/en-us/library/8b6y4c7s(VS.80).aspx )

OK now let's continue.

select: is the keyword for selecting of course:) Linq however can do a select that can do more than flat retrieval. It can transform the data it retrieves (projection, shaping data) in a variety of ways. With SQL select the result may be transformed. i.e:

select employeeID, rtrim(LastName) + ', ' + rtrim(FirstName) as Fullname from employees

Fullname is a transformation of data but still we have a flat result made up of rows and columns. However with Linq we can do much more complex shaping on the data retrieved.

select clause too also have method counterpart Select(). Select() method just like Where() have 2 overloads. Second overload, like Where, has an index parameter and not supported by Linq to entities (you can find a list of supported and non supported methods for Linq to entities here.

Do you always need to use select clause or Select() method? No, you don't have to. Using query syntax you need to end the expression with a 'select' or 'group' clause. You can have do these:

var customers = Customers; // in LinqPad Northwind set as the database you may try just typing: Customers [F5]
var usaCustomers = Customers.Where( c => c.Country == "USA" );

You may think of these variations like:

use customers

and

use customers
scan for Country = "USA"

We have done these before like:

from c in Customers select c

from c in Customers where c.Country == "USA" select c

These are "expressions" you can directly type in LinqPad and hit F5 to get results.

You can shape the result in a variety ways and to create new shapes you use "new { … }" as we saw earlier in creating anonymous types:

from c in Customers select new { c.CompanyName, c.ContactName }

from c in Customers select new { MyCompany = c.CompanyName, c.ContactName }

are simple transformations and correspond to:

select c.CompanyName, c.ContactName from Customers

select c.CompanyName as MyCompany, c.ContactName from Customers

in SQL. Now more interesting selects – try these in LinqPad:

from c in Customers
where c.Country == "USA" && c.Region == "WA"
select c.Orders

from c in Customers
from o in c.Orders
where c.Country == "USA" && c.Region == "WA"
select new {CustomerInfo = c, OrderInfo = o}

you can have many variations, you can embed little lambda expression to transform further or utilize Linq to XML to generate XML results. i.e:

from c in Customers
        select new XElement ("customer",
            new XAttribute ("id", c.CustomerID),
            new XAttribute ("company", c.CompanyName),
            new XElement ("contact", c.ContactName),
            new XElement ("country", c.Country),
                  new XComment ("Total Orders:"+c.Orders.Count().ToString()))

Query keywords - where

2009-03-08T22:01:00.003+02:00

where: Just like in SQL “where” is used to filter the query results. Though not supported by all flavors (like Linq to SQL doesn’t support) if method syntax is used, then you can also filter on based on position in collection (i.e: if you are filtering an array you can say to choose from each Nth element where N is even – see the sample below).

Samples assume Northwind sample database is used for the ones against SQL data – to do that with LinqPad:

Click “Add Connection”, point to your SQL server instance that have the “Northwind” database. If database is not already selected in query window, click “Use Northwind” link displayed in window at right as seen in the following screen shot:

With this setup you can simply type the codes below and press F5 to see results.

from c in Customers
where c.Country == "USA"
select c

VB version:

from c in Customers _
where c.Country = "USA" _
select c

Method syntax (C#):

from c in Customers.Where( c => c.Country == "USA" )
select c

Method syntax (C#) – same thing typed another way:

from c in Customers
.Where( c => c.Country == "USA" )
select c

Method syntax (C#) – same thing typed another way:

from c in Customers
.Where( c =>
c.Country == "USA" )
select c

Method syntax (VB):

from c in Customers _
.Where( Function(c) c.Country = "USA" ) _
select c

Simple where filtering on Customers whose Country is USA

Note that in all syntaxes that is a single statement (single command). You can type it in different ways using continuation (in C# you don’t need any continuation line marker – where in VB you use underscore and VFP semicolon).

VB method syntax is a little weird and hard to understand for me, I wouldn’t show VB method syntax in other samples. It should however be easy to understand for VB developers.

Within method call the variable “c” is local to Where() method and could be something else. i.e:

from c in Customers
.Where( cus =>
cus.Country == "USA" )
select c

Using method syntax we would see it is easier (it is not clear here why it is) but keeping that to later. Here it is just another way of writing the same thing.

from c in Customers
where c.Country == "USA" && c.Orders.Count < 5
select c

VB version:

from c in Customers _
where c.Country = "USA" and c.Orders.Count < 5 _
select c

Method syntax (C#):

from c in Customers
.Where( c => c.Country == "USA" &&
c.Orders.Count < 5 )
select c

Adding additional criteria. Note the navigational access to Orders of Customer simply using c.Orders and then Count property on customer’s Orders

from c in Customers
where c.Country == "USA" &&
c.Orders.First().OrderDate < new DateTime(1997,1,1)
select c

VB version:

from c in Customers _
where c.Country = "USA" and _
c.Orders.First().OrderDate < new DateTime(1997,1,1) _
select c

Method syntax (C#):

from c in Customers
.Where( c => c.Country == "USA" &&
c.Orders.First().OrderDate <
new DateTime(1997,1,1) )
select c

Country equals “USA” and additional criteria on Customer’s “first” Order using “First()” extension method. c.Orders.First() return the first order of that Customer as a Customer object where you can check any property (field, column). Another thing to note here is that we use strongly typed .Net DateTime() without doing any tricky conversion or anything like that to check OrderDate which is a DateTime in backend. If you check the SQL sent to backend “each” criteria are sent as parameters which is the way to do this in VFP too.

To remind Where() method, it has 2 overloads ( syntax shown here is not official and correct .Net syntax but kind of syntax that I converted to “VFP like” to make it easier VFP developers to understand input, output parameters. You can find the actual official .Net syntax on MSDN help ).

Where( lFilterExpression )

Where( nRowIndex, lFilterExpression )

We have already seen how Where( lFilterExpression ) version is used in the samples above. Note that, c => c.Country == "USA" is an expression as a whole that could be assigned to a variable (I am telling it here because I know VFP developer’s start to think how they could create dynamic expressions, do macro substitution etc. It is not exactly that but you should feel that you don’t need & operator. In VFP a similar one might be : lcWhere = “c.Country = ‘USA’” followed by select ... where &lcWhere).

Second overload is not supported in Linq to SQL. I will instead give a sample using an array:

string[] months = {"January","February","March",
"April","May","June",
"July","August","September",
"October","November","December"};
var myMonths = from m in months
.Where( (month, index) => index % 3 == 0 && month.Contains(‘r’) )
select m;
myMonths.Dump("Quarter starts");

Here we use a criteria telling “Month’s array index should be a multiple of 3 and Month name should contain the letter ‘r’. We could chain multiple Where() and write a query like this:

var inSeattle = Customers
.Where(c => c.Country=="USA")
.Where(c => c.Region=="WA")
.Where(c => c.City=="Seattle");

inSeattle.Dump();

Which exactly generates the same SQL as this one:

var inSeattle = Customers
.Where(c =>
c.Country=="USA" &&
c.Region=="WA" &&
c.City=="Seattle");

inSeattle.Dump();

Query keywords - from

2009-03-08T05:27:00.001+02:00

Let’s start to check each query keyword with samples.

from: Is like the “from” in SQL, refers to the source.

from c in Customers
from n in numbersArray
from f in Directory.GetFiles(“c:\\temp”)
from p in Diagnostics.Process.GetProcesses()
from di in DriveInfo.GetDrives()

In these incomplete samples c, n, f, p, di are variables referring to an object of type it is used with. For example if Customers is a collection of Customer objects then “c” in “from c in Customers” refer to a Customer type. In SQL, you may think of it as the local alias of table like:

Select c.CustomerID, c.CompanyName, c.ContactName from Customers c

In method syntax as far as I know there isn’t something to use instead (you have it as a parameter – this Ienumerable<Tsource> source). The reason that you first write “from” is intellisense support. That makes sense if you think how we write an SQL select. Think that you have 50 columns in a table and you want to select 10 of them, visualize the ideal way that you could write it. We all have some ways to make the life easier fro ourselvees in writing those type of queries but none of them would be as good as getting a direct support from the IDE itself. Saying it like:

from Customers c Select c.CustomerID, c.CompanyName, c.ContactName

we would be getting help from intellisense when writing c.CustomerID, c.CompanyName, c.ContactName. I think it was a syntax choice (probably to match natural English) to write it as:

from c in Customers

instead of

from Customers c

Unlike SQL where there is a single “from” clause in Linq you can have multiple. Multiple “from” create a type of join and there is also “join” keyword. Here are samples for multiple “from”:

from c in Customers from o in c.Orders

is an implicit join (equijoin, inner join) – in case of Linq to SQL and Linq to Entities it infers the join as c.CustomerId = o.CustomerId). If you make a typo (might be intentional but generally a newbie typo) and write it as:

from c in Customers from o in Orders

then it is still an implicit join (non equijoin, cross join) but selects all records from both tables and match each producing cartesian result – same as if we do a typo in SQL version like:

select * from Customers, Orders

leaving out the join (either with a join clause or where specifying the relation) ends in a cross join. It might be intentional, say to create a calendar like:

2007,January
2007,February
...
2009,January
2009,February
...

int[] years = {2007,2008,2009};
string[] months = {"January","February","March",
                   "April","May","June",
                   "July","August","September",
                   "October","November","December"};
var myCalendarHeaders =
         from y in years
         from m in months
         select String.Format("{0},{1}", y,m);
myCalendarHeaders.Dump();

Query or method syntax

2009-03-06T04:38:00.002+02:00

There are a series of "Query Keywords" which most of them should be familiar to any VFP developer. They are very similar to the SQL clauses we are familiar with and results they provide are more or less similar. Beside query keywords, there are a series of extension methods defined in Linq namespace with same or similar names to those keywords and more. As said before you can use query syntax most of the time and need to use method syntax in some cases where query syntax is not supported. That is what language supports and forces you to do. IMHO however, method syntax is easier and nicer. Once you get a hang of it, query syntax and the SQL we know sounds to be a hard language:) I will try to go with both syntaxes but might honor method syntax more.

To understand the method syntax and to use it effectively we need to learn how to read method signature. Here are the signatures of System.Linq.Enumerable.Where method as an example:

public static IEnumerable<TSource> Where<TSource>(
 this IEnumerable<TSource> source,
 Func<TSource, bool> predicate
)

public static IEnumerable<TSource> Where<TSource>(
 this IEnumerable<TSource> source,
 Func<TSource, int, bool> predicate
)

Syntax is not easy to understand but don't be afraid. If you can rewrite it in your mind unofficially you can understand it quicker then you expect:

There are 2 signatures or two 'overloads' of Where method, meaning it could be called with 2 parameter sets.
<TSource> : Is all about generic typing. In case of <Customer> all those TSource become Customer type.
public static IEnumerable<TSource> : read this part as "it would return a series of records" – TSource is the type of Object. Think it like a table. If table is Customers then TSource is a Customers row where fields are its properties.
Input parameters are (for first overload):

this IEnumerable<TSource> source : Parameter name is "source" and it is a collection of TSource objects (think Customer as sample). "this" tells you that it is an extension method and can be used on "this" instance (of Customer type) just like as VFP's "this".
Func<TSource, bool> predicate : Parameter name is predicate and it is an inline method (a lambda expression) that takes an object of type TSource (say Customer) as input and returns a bool result (true or false).

In second overload, second parameter is -
Func<TSource, int, bool> predicate : Parameter name is again "predicate", which is an inline method that takes an object type of TSource, plus an integer as input parameters and returns a boolean value.

Lets recap it in a pseudo code and a little bit VFP like typing:

procedure Where( source as CollectionOfTSource ) as CollectionOfTSource

Loop and call predicate( TSource ) per item in source Collection

Add those that returns True to return Set

Procedure predicate( item as TSource ) as Logical

in second overload procedure named "predicate" would look like:

Procedure predicate( item as TSource, parameter2 as integer ) as Logical

Let's give an example to understand better:

int[] numbers = {1,10,29,5,53,33,32,277,232,32,43};

IEnumerable<int> oddNumbers = from n in numbers where n % 2 != 0 select n;

oddNumbers.Dump("Odd numbers using query syntax");

This is vanilla ice query syntax that looks like an SQL and correspond to first overload of Where() method. Here we use "where" clause with a simple filter. Same query can be done with method syntax like this:

IEnumerable<int> odds = System.Linq.Enumerable.Where( numbers,n => n % 2 != 0 );

odds.Dump("Odd numbers using method syntax - 1");

We are sending "numbers" as ( IEnumerable<TSource> source ) parameter and " n => n % 2 != 0 " as ( Func<TSource, bool> predicate ) parameter to Where() method defined in System.Linq.Enumerable class. TSource is defined as int in this case. Since "Where" method is defined as an extension method for IEnumerable<TSource> (in other words a collection of same type of objects – their type is referred to as TSource, in our sample TSource is int to remind) we could use the Where method directly on the collection instance (our collection here is "numbers" which is a collection of int). Here is our simplified version:

IEnumerable<int> odds2 = numbers.Where( n => n % 2 != 0 );

odds2.Dump("Odd numbers using method syntax - 2");

// Could write those 2 lines as one – didn't do like that just to prevent confusion:

// numbers.Where( n => n % 2 != 0 ).Dump("Odd number using method syntax - 2");

Method syntax is easier IMHO and moreover it lets us to use the second overload of Where(). In second overload "int" parameter is an index to item's location (in forums you should have seen questions like: "how can I get last 3 orders per customer", this parameter lets you go further and in your inline method you can have criteria for the position – not supported on all Linq flavors, i.e.: Linq to SQL doesn't support it). Let's use it in our next sample to select odd numbers from our set "where" the number's position in array is greater than 3rd element and less than 6th element (remember in C# sequences start at 0):

numbers.Where( (n, i) => n % 2 != 0 && i > 2 && i < 5 ).Dump("Odd numbers from a subset");

Note that we didn't do any sorting (yet) last query would return 5,53. Play with simple queries using Where() method to get used to it, you wouldn't regret you learned method syntax (especially when you see chained multiple methods which we don't see in SQL). You can mix query and method syntax. By the way, whatever syntax you use intellisense kicks in and helps you on the way.

PS: n => n % 2 != 0 is a lambda expression and is typed (actually its type name is LambdaExpression). Typed > has a type (class) > is reusable, can be saved, loaded, passed around as a parameter (not something that we see with SQL and for that reason I think hard to understand initially).

Simplest table and query

2009-02-27T04:40:00.001+02:00

We made a head start to Linq skipping its basics and gave a sample from Entity Framework. Both Linq and Entity Framework are huge and much more capable than that sample could show. Linq can be done against any enumerable source means that we can treat a much wider set of source as ‘data’ and query over them using the same set of methods. Linq have two syntaxes, one called Query syntax and the other method syntax. You can use the one you like but some features are only supported through method syntax (and as you get used you it method syntax sounds to be easier).

We will use LinqPad for a series of reasons:

A cool interface that looks similar to and can use in place of SQL server management studio. We can interactively write and run C# (or VB) expressions, statements, program or even T-SQL. We can have multiple of them active at the same time shown under different tabs. It has a nice way of showing the results in grids (most of the time grids). Those grids also show the power of .Net from another aspect. It automatically writes in additional syntaxes and IL code where appropriate (i.e.: even if you wouldn’t use .Net at all and good in object query more than you are in T-SQL, you could write your query in Linqpad, test and get the corresponding T-SQL code from results’ window SQL tab. Below is a sample)

from c in Customers
where c.Country == "USA" && c.Orders.Any(o => o.OrderDate == new DateTime(1997,1,1))
select c

is simplier than writing corresponding T-SQL and more natural and shows what is really sent to SQL server. Here is what it produced on my box against SQLExpress 2005, Northwind sample database:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE ([t0].[Country] = @p0) AND (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Orders] AS [t1]
    WHERE ([t1].[OrderDate] = @p1) AND ([t1].[CustomerID] = [t0].[CustomerID])
    ))
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]
-- @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [01.01.1997 00:00:00]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

You could write it in multiple ways and interestingly enough the code generated is almost the exact query that I would suggest if I saw someone asking something like:

“Using Northwind data, how could I get the customers who are from USA and had an order on 1/1/1997?”

Saving, loading of your own codes is a snap. With its samples tab it is not simply a tool but a tutorial in itself. Assuming you picked the autocomplete activation for a few bucks, it simplifies the writing of code as if you are in VS.

In summary, LinqPad is a cool utility deserving more than this promotional words. Let’s put up the LinqPad and start to investigate. LinqPad’s samples are nice, do not forget to check them out.

The simplest enumerable thing would be an array. Starting with an integer array lets check Linq syntax, extension methods and others:

int[] digits = {0,1,2,3,4,5,6,7,8,9};
var query = from d in digits select d;

foreach(var digit in query)
{
Console.WriteLine(digit);
}

It is a lot of code, yes., but of course it is because I started with a long and verbose code. Now I will make analogies to SQL but Linq is not SQL, it might be strange to compare them (you better try to think in terms of objects and gets accustomed to it ASAP). You can think of “digits” as a single column table. The simplest select query would be:

select * from digits

In Linq query syntax we start with “from” and:

from d in digits

is a way of saying - OK I want ‘rows’ from ‘digits’ table and I will refer to each ‘row instance’ as ‘d’. In that sense, ‘select d’ means ‘all columns of’ or ‘select *’. Assigning it to ‘query’ is like ‘into cursor query’ (with the exception ‘select … into cursor query’ wouldn’t really execute until foreach(…) that demands its result – foreach() is not the only way to demand results).

foreach(var digit in query) { // do whatever }

is a simple loop which you can think of as ‘for each … endfor’ or ‘scan…endscan’. In VFP that code would be something like:

select * from digits into cursor query && assuming we have a single column named d
scan
? d && or query.d
endscan

Hmm, you say, but in VFP I have the luxury of using xbase and could simply do it like this:

use digits
scan
? d
endscan

In C# you didn’t really need that ‘from … ‘ and could simply use ‘digits’ itself in the same manner:

int[] digits = {0,1,2,3,4,5,6,7,8,9};
foreach(var digit in digits)
{
Console.WriteLine(digit);
}

This is plain old C#, no Linq in sight. However, if it were a table like thing we would expect to be able to do filtered selections, aggregations like average, sum… etc. If you didn't pay for LinqPad's autocomplete activation do this in VS:

int[] digits = {0,1,2,3,4,5,6,7,8,9};
var myCursor = digits.

As soon as you put the dot intellisense would kick in (in case of VS it would kick in earlier) and list PEM of digits.

Methods are shown with a pink icon and those methods having blue arrow next to pink icon are Extension Methods defined in Linq namespace. If you disable Linq extensions by commenting using System.Linq you see those methods are gone from the list:

In LinqPad some namespaces you need frequently are implicitly imported and you don't need those using lines – and you could add other namespaces you may need using F4. In LinqPad there is a cool method named "Dump()" that "eats" anything you throw at it! Use it to easily check your results and play with 'digits table' in LinqPad, try things like:

digits.First().Dump();
digits.Sum().Dump("Sum of Digits");
digits.Average().Dump();

or have more fun with queries like:

int[] digits = {0,1,2,3,4,5,6,7,8,9};
var query1 = from d in digits where d > 5 select d;
query1.Dump();
var query2 = from d in digits where d % 2 == 0 orderby d descending select d;
query2.Dump();
var query3 =
from d in digits
select new {
             d,
             even = (d % 2 == 0),
             sqr = d * d,
             cube = d * d * d,
             bitset = 1 << d
             };
query3.Dump();
var query4 =
from dColumns in query3
group dColumns by dColumns.even into groupedCursor
select groupedCursor;
query4.Dump();

query1 adds a simple 'where' clause. query2 demonstrates an 'order by'. query3 demonstrates projection (like SQL's as clause – select d, (d%2 = 0) as even, d*d as sqr … ) query4 demonstrates querying from another query (query3 – like making a select from a previous cursor) plus 'group by'. Note that group by result is a little different than SQL group by result and is a Key, Collection list ( like a table of grouping keys that contain their related items ):

Are we having fun yet? Just wet our toes:)

What if the backend wasn’t VFP

2009-02-20T02:06:00.001+02:00

Linq comes in various flavors like Linq to objects, Linq to XML, Linq to dataset, Linq to SQL (L2S), Linq to entities (L2E). When it is relational data Linq to Dataset, L2S and L2E are the ones we need to think about. Of those 3 Linq to dataset is the hardest IMHO. L2S, though very nice, easy and fast is somewhat outdated. L2E is newest and suppported by increasing number of providers. Check current list of "ADO.NET Entity Framework Providers". As a data backend my preference is SQL server and what I write are almost always based on SQL server. To keep long story short, instead of a DataSet we would create an ADO.Net entity model:

For a quick test, select “Generate from database”, point to SQL server Northwind database and select to create a model with Customers, Orders and Order Details tables.

This operation is certainly easier than creating the testdata typed dataset that we did yesterday. Save it and write the new code. Here is new code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace L2E
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindEntities nw = new NorthwindEntities();

            var usaCustomers =
                from c in nw.Customers
                where c.Country.Trim() == "USA"
                select c;
            var AComplexQuery =
                from c in nw.Customers
                where c.Orders
                       .Sum(o =>
                           o.Order_Details
                           .Sum(od =>
                               od.Quantity * od.UnitPrice)
                           ) > 50000m
                select c;

            foreach (var customer in usaCustomers)
            {
                customer.Region = "1" + customer.Region;
            }
            foreach (var customer in AComplexQuery)
            {
                customer.Region = "2" + customer.Region;
            }
            Console.WriteLine("Easy USA Customers: {0}", usaCustomers.Count());
            Console.WriteLine("Easy AComplexQuery: {0}", AComplexQuery.Count());
            nw.SaveChanges(true);
        }
    }
}

Can you say this is harder than yesterday’s code? Isn’t this code easier than the VFP code that would do the same thing (no matter what the backend is)? OK maybe you would say not easier but close, for me much easier:) Implementation of foreach() content is shorter but not much important. However if you look at purple blocks you would see that now the query is easier and now you have a better navigational path.

L2S generates SQL against backend while L2E generate a sort of SQL called “Entity SQL”. From MSDN:

Entity SQL is a storage-independent language that is similar to SQL. Entity SQL was designed to query and manipulate rich object graphs of objects based on the Entity Data Model (EDM).

You can find the details of Entity SQL here.

Linq is fun – continued, part 2

2009-02-19T04:52:00.001+02:00

Here is the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace VFPLinq
{
    class Program
    {
        static void Main(string[] args)
        {
            Testdata td = new Testdata();
            TestdataTableAdapters.CustomerTableAdapter daCus =
                new VFPLinq.TestdataTableAdapters.CustomerTableAdapter();
            TestdataTableAdapters.ordersTableAdapter daOrd =
                new VFPLinq.TestdataTableAdapters.ordersTableAdapter();
            TestdataTableAdapters.orditemsTableAdapter daOi =
                new VFPLinq.TestdataTableAdapters.orditemsTableAdapter();

            daCus.Fill(td.Customer);
            daOrd.Fill(td.orders);
            daOi.Fill(td.orditems);

            var usaCustomers =
                from c in td.Customer
                where c.country.Trim() == "USA"
                select c;

            var AComplexQuery =
                (from c in td.Customer
                 where c.GetordersRows()
                        .Sum(o =>
                            o.GetorditemsRows()
                             .Sum(od =>
                                 od.quantity * od.unit_price)
                                 ) > 50000m
                 select c).Distinct();

int colWidth = td.Customer.regionColumn.MaxLength;

            foreach (var customer in usaCustomers)
            {
                string newRegion =
                    String.Format("{0}{1}", 1, customer.region.Trim());
                customer.region =
                    newRegion.Length > colWidth
                    ? newRegion.Substring(0, colWidth)
                    : newRegion;
            }

            foreach (var customer in AComplexQuery)
            {
                string newRegion =
                    String.Format("{0}{1}", 2, customer.region.Trim());
                customer.region =
                    newRegion.Length > colWidth
                    ? newRegion.Substring(0, colWidth)
                    : newRegion;
            }

            Console.WriteLine("Updating {0} customers for usaCustomers",
                usaCustomers.Count());
            Console.WriteLine("Updating {0} customer for complex query",
                AComplexQuery.Count());

            daCus.Update(td);
            td.AcceptChanges();
        }
    }
}

If video trial wasn’t a disappointment and it didn’t drop frames becuase of the lower quality I chose, you would be able to see it is easy to write all these code with the help of intellisense. Otherwise I agree, without intellisense writing all these is not fun. Before running this code run this one in VFP:

SELECT cust_id,region,country ;
FROM (_samples+'data\customer') ;
WHERE ISDIGIT(region)

It shouldn’t show any records. Go back to C# code and run it with Ctrl+F5 or “Debug\Run without debugging”. We are using Ctrl+F5 instead of simply the F5 because we want DOS screen (we created a console application) to show the results and wait for an input from us before closing (without adding a wait state code like readline – being lazy is good:)

You would see updated customer counts. Go to VFP and check results running the same code again. In second run this is my screen snapshot:

You see customers matching to 1st query (USA customers are flagged with a 1 prefix and priviliged ones with a 2 prefix. Some customers are both from USA and priviliged as well they are marked with “21” prefix (we first added 1).

So what? What did we do all this time? Just updated a few records based on some simple and complex (well not so complex) queries? Well that may be enough to impress some developers but certainly VFP developers would be thinking “hah, I have been doing that for years and don’t need to know all those syntax and rest”. If that weren’t enough, we first filled a dataset with all the records from our database, which means all the data would travel down the wire if it were on a remote machine (not so remote when it is VFP but still it is a network). You would be right in your thoughts. However, just for a second think:

We started with a sample that is not designed for efficiency. We just wanted to show that “we can”. Simply loaded and updated some VFP data based on some Linq queries.
Linq didn’t really have some role in what we did except that:
- filtering the data
- showing what would need a relatively complex SQL could be written by a non-SQL oriented developer in an easy way to express ( I mean he doesn’t need to know what a join is and how to connect them in SQL – Linq has joins but do not think as if it were what it is in SQL).
VFP is not a good choice to use as a backend especially when working with .Net and expecting efficiency in coding would not be right. VFP and .Net is from the same company but VFP simply do not have the provider support we need (maybe will be added later or someone who ever could make DDEX provider work right may have had a better experience – I was lazy not to try too much with it).
Dataset itself is not a good choice to show power of Linq and processing data. ADO.Net predates Linq.

Maybe I am wrong. Anyway let’s dissect the code.

            Testdata td = new Testdata();
            TestdataTableAdapters.CustomerTableAdapter daCus =
                new VFPLinq.TestdataTableAdapters.CustomerTableAdapter();
            TestdataTableAdapters.ordersTableAdapter daOrd =
                new VFPLinq.TestdataTableAdapters.ordersTableAdapter();
            TestdataTableAdapters.orditemsTableAdapter daOi =
                new VFPLinq.TestdataTableAdapters.orditemsTableAdapter();

            daCus.Fill(td.Customer);
            daOrd.Fill(td.orders);
            daOi.Fill(td.orditems);

is well known “typed dataset” loading. I won’t let you get confused what a typed dataset is and explain a bit here w/o any links. In practical terms think in VFP you are selecting data from a table and you only know its name. You would do a query and check what fields you got. It is a generic, at run time discovery of what you have on hand. Updating such data would some checks on data types and column names. Or to say it in another way it is as if you are doing:

SQLExec(m.lnHandle,”select * from SomeTable”,”myCursor”)

and not using a cursor schema. You know the pain. You can create such datatables and datasets and it would be Untyped. When you have a dbc with tables and views in it –either local or remote- you can generate a prg version of your data structures using gendbc (or using other ways any table). There is a definition of your data in code. Likewise in .Net you can define a “dataset” with its tables, relations, data types, constraints and so on that matches to its source (and generated from an existing source generally). Such a dataset is called a “typed dataset”. “Testdata” is a designer generated (dataset designer) type (remember terminology class, structs, delegates are all “type”s).

Testdata td = new Testdata();

is something like:

td = createobject(“Testdata”)

and

TestdataTableAdapters.CustomerTableAdapter daCus = new VFPLinq.TestdataTableAdapters.CustomerTableAdapter();

is something like:

daCus = newobject(“MyCustomerCursorAdapter”, “VFPLinqTestDataTableAdapters.vcx”)

DataAdapters like CursorAdapters in VFP are “adapter” types that manage from/to data store (designer already wrote SelectCommand, DeleteCommand, UpdateCommand, … for you). If you are working with cursoradapter classes in VFP then it is easier to understand. Anyway, .Net handles the code for you and in fact we could have done a “zero code” 3 table (Customer, Orders, Order Details) updatable form, just by a few (click, drag & drop)s.

Here in code our Linq queries:

            var usaCustomers =
                from c in td.Customer
                where c.country.Trim() == "USA"
                select c;

“var” is a keyword and means C# would infer the real “type from usage”. Instead of var you may write the type explicitly yourself but var is easier and you can use as a “teacher” what the type would be (Tip: in VS after writing your query move mouse on to var). In VFP the first one looks like:

text to m.usaCustomers noshow && with ansi on – case sensitive since we are executing against VFP
select * from customer where trim(country) = “USA”
endtext
SQLExec(m.handle,m.usaCustomers)

Not exactly but the point is that it is a “query definition”. Defined but not executed. Something that requires a result from that query “triggers” the execution and each execution is another call (if in between data changed from outside this code next execution brings the results based on new content – sort of requery). If you didn’t like it don’t worry it is just one of the ways you can use it. Anyway back on track, you would find the syntax very similar to SQL. This is more like OQL (Object Query Language). It starts with “from” instead of “select” and interestingly “select” is placed at the end. You would appreciate it such an arrangement lets you efficiently use intellisense on queries and “select” at the end is referred to as “selector” (in syntax documentation you would see things like selector, projector…).

from c in td.Customer

here “c” is just an identifier name I chose to name each td.Customer type in set. In practical terms c refers to a row of customer table as if I have done:

select customer
scan
scatter name c
…
endscan

(w/o scan …endscan execution). Or you could think of it as a local alias of a SELECT-SQL:

select … from customer c …

(BTW I am making analogies for you to understand initally and don’t get too much confused – you would do yourself a favor if you can stop thinking in SQL and think the Linq way but initially what I am doing shouldn’t harm you).

where line in 1st query looks like and works like our classical SQL and finally select c means select * (or yield c type that we started with). From a practical view point, a simple SQL selecting the USA customers. The result being an object in fact is not your fault:)

Second query is sligthtly more complex but should be easy to understand for VFP community, with the exception of Get…Rows().Sum( … ). I just wanted to poke a different one initially:) It would be good to talk about it later.

OK next we got region column’s maxLength aka field size. Why we ever did that? We could simply do our replace ignoring overflowed data. In VFP that would work, character fields trailing characters are not important or even if the data wouldn’t fit VFP would silently let us do the replacement dropping characters at the end. However in ADO.Net it has been thought of as a developer error and causes an exception. Instead of a try..catch I simply wanted to create a new entry that is not more than max length (see in code there is a check if it is exceeding max length).

foreach() is like the for each we know in VFP and we can say that it is “scan … endscan”. Yes you are not losing scan…endscan just name and syntax if you are not too resistant to think otherwise. In our code it is foreach() that “triggers” the execution of related query.

int colWidth = td.Customer.regionColumn.MaxLength;

After setting the region value for the matches within a “scan … endscan” like iterator structure finally we show how many “records” matched using “Count()” extension method on our queries (if you hover your mouse on “var” you would see it is an IEnumarable<T> and T is VFPLinq.Testdata.CustomerRow – in other words something you can enumerate and thus already have an extension method “Count()” in Linq namespace).

daCus.Update(td);
td.AcceptChanges();

daCus.Update() is like TableUpdate(2,.F.,”v_customer”)

Hope you started to like it. If you did you would love it when you see muchmore simple and effective ways of doing these (I shouldn’t start with a though one maybe). See you in next post folks.

Linq is Fun - continued

2009-02-19T02:15:00.001+02:00

OK since the video has failed let’s publish photos and explain.

Start Visual studio (or express),Select C# Console application for a new project and name it say “VFPLinq”. Right click, project in solution explorer (bold) and select add\add new item and select “Dataset”, name it Testdata.xsd

This would cause a few files added to project and a new dataset design surface pop up, it may take some time, be patient. Now if server explorer window is visible select it or from menu Data\Add new data source\Database\New connection. If you are using VFP9 SP2 and registered DDEX provider you may try your chances with it. I installed it last night and it gave me a lot of trouble I won’t use it (looks like besides not thinking of VS2008 they forgot 2 dlls which doesn’t exist in VS2008 only installations, I setup VS SDK to overcome and rebuild and finally got it to work – but results were not good giving other errors about update command generation. I surrendered seeking about the reason and prefer to work with .Net data provider for OleDb instead. It works well). OK anyway next step is to add the connection.

You would see a new data connection added to your server explorer window, expand connection and then tables node as shown below, drag&drop Customer, orders, ordItems tables on to design surface. Just drop and save.

As you would notice it automatically sets up relations. What we have done is to create a definition of our connection and data – to say it otherwise it is something like adding a connection and remote view(s) to a database designer in VFP.

Now we can switch to code window (the tab shown as “program.cs”). We would write code that:

Fills up a dataset with ALL the data from customer, orders and orditems tables.
Defines 2 queries
1. A simple one that select “USA” customers
2. A complex one that selects priviliged customers – that is customers that made more than 50,000$ worth of purchases (total).
Based on customers matching to those queries we will update first customer set (USA customers) region column with a “1” prefix, and 2nd one with “2” as a flag to show who we matched and that we are really updating VFP data easily. I chose updating only region column in such a fashion so it wouldn’t be a very destructive operation on your sample data. I know it is a sample database anyway but probably you wouldn’t backup at all and don’t want tyo modify much.
Finally code writes to console how many records would be affected per change.

In next post you will see code and more explanations. Hang on.

Linq is fun

2009-02-19T01:32:00.001+02:00

LINQ is short for “Language INtegrated Query”- and it is fun. When you say “Query” to a VFP developer she/he immediately visualizes a sequence like “select … from … where …” or SQL in other words. Besides SQL, VFP developers work with xbase commands a lot and don’t like the idea to work w/o having scan…endscan, locate … etc series of commands. I think VFP developers’ majority saw .Net as an hostile environment especially on data processing area and IMHO they were right until 3.0 came out. Now they can feel at home (not so quite, somethimes it will be hard to grasp, sometimes you would love thinking it is much easier then SQL). I get some questions about performance, how do I … etc and I hope to answer those questions as we go.

BTW to learn Linq I read books like Introducing Microsoft LINQ by Paolo Pialorsi and Marco Russo Microsoft Press © 2007 and Programming Microsoft LINQ by Paolo Pialorsi and Marco Russo Microsoft Press © 2008 as well as C# 3.0 in a Nutshell by Joseph Albahari and Ben Albahari (and actually still reading). Those books are good (well good is not enough to describe, especially #.0 in a Nutshell is one of the best I have ever read) but just reading and testing in Visual Studio is an hard and slow way of learning. I happened to be interested in F# in between and honestly working with it missing pieces of the puzzle started to find their places faster for me, especially lambda expressions and statements. And of course it was the LinqPad utility that made my learning roll faster. So did I learn it? No, not yet, I still have way to go but I have come a long way I think. Enough story:) Let’s kick off.

Linq is about querying but that doesn’t mean it only helps to query (in other words to select). Using Linq we do CRUD operations ( CreateRetrieveUpdateDelete or Insert/Select/Update/Delete) and even some DML operations using classes that are built to support Linq. Practically speaking it makes the VFP developers’ life easier in .Net :) In VFP “query” reminds us tables/cursors. What is cool about Linq is that you can “query” anything that can be enumerated or queried, in .Net terms those that implement IEnumerable<T> or IQueryable<T>. What does it mean? Practically it means you can query structures like tables, arrays, collections … IOW almost anything that has a plural form. Seeing is believeing. Launch LinqPad. If you check “Samples” tab you would see that Joseph already took the show in a 5 minute induction (and of course other cool samples in following nodes). We will use LinqPad mostly because it is easy to test code there and save or discard as you wish.

Now we will go and directly query/update/delete some data in VFP samples\data\tesdata.dbc ! The reason that I start with such an aggressive sample is that I need to showcase as early as possible to some forum bloggers/posters that .Net is not as bad as they think in data processing. I recorded a video of what I did. I will upload it to here, plus explain what we did and why etc. I will also give the code. OK here we go.

(Video didn’t work good – updating to support with photos)

I am not sure if video would work (my first attempt). If it doesn’t I will support most important parts with photos. Let me post this and see.

Some more C# 3.0 features

2009-02-15T21:21:00.001+02:00

In VFP we have eventhandler and bindevents that does binding of methods to the methods of an object we define. Likewise in C# there are delegates. Delegates are types (remember classes and structs are called types – delegate is also a type, as well as interfaces). Here is the definition of delegate from C# Programming Guide:

A delegate is a type that defines a method signature, and can be associated with any method with a compatible signature. You can invoke (or call) the method through the delegate. Delegates are used to pass methods as arguments to other methods. Event handlers are nothing more than methods that are invoked through delegates. You create a custom method and a class such as a windows control can call your method when a certain event occurs.

You will find a lot of samples about delegates but I think it is one of the things that is hard for VFP coders to understand (because in VFP it looks like as if it is a user defined function call but it is not). Here is a sample:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DelegateSampling
{
public delegate string MyNameFormatter(string first, string last);
public delegate double MyCustomMath(int[] examScores);

    public class Student
    {
        public Student(string f, string l, int[] scores)
        {
            this.FirstName = f;
            this.LastName = l;
            this.Scores = scores;
        }

        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int[] Scores { get; set; }
    }

class Sample
{

        static void Main(string[] args)
        {
            List<Student> pupils = new List<Student>();
            pupils.Add(new Student("John", "Doe", new int[] { 80, 90, 90 }));
            pupils.Add(new Student("Frank", "Smith", new int[] { 70, 80, 85 }));
            pupils.Add(new Student("Chris", "Brown", new int[] { 70, 60, 75 }));
            pupils.Add(new Student("Tim", "Fisher", new int[] { 80, 90, 100 }));

            Sample test = new Sample();
            MyNameFormatter name = FullName;
            MyCustomMath avg = AvgScore;
            MyCustomMath minSc = MinScore;
            MyCustomMath maxSc = MaxScore;

            foreach (Student s in pupils)
            {
                Console.WriteLine("{0} - Avg: {1:F2}, Min: {2:F2} Max:{3:F2}",
                    name(s.FirstName, s.LastName),
                    avg(s.Scores),
                    minSc(s.Scores),
                    maxSc(s.Scores));
            }
        }

//define methods that have the same signature as our delegates

        // Name formatter
        public static string FullName(string fName, string lName)
        {
            return String.Format("{0} {1}", fName, lName);
        }

        // Custom math on scores
        public static double AvgScore(int[] scores)
        {
            int total = 0;
            for (int i = 0; i < scores.Length; i++)
            {
                total += scores[i];
            }
            return total / (double)scores.Length;
        }
        public static double MinScore(int[] scores)
        {
            double min = (double)((scores.Length == 0) ? 0 : scores[0]);
            for (int i = 0; i < scores.Length; i++)
            {
                min = (min < scores[i]) ? min : (double)scores[i];
            }
            return min;
        }
        public static double MaxScore(int[] scores)
        {
            double max = (double)((scores.Length == 0) ? 0 : scores[0]);
            for (int i = 0; i < scores.Length; i++)
            {
                max = (max > scores[i]) ? max : (double)scores[i];
            }
            return max;
        }
    }
}

You should be asking what is special about it? You could simply directly call the methods instead of calling them “through the delegates”. Well you could do that but think about it as if it was a VFP bindevent() call. You could write the method code beforehand and call those methods. However, you are either binding to methods that you don’t have direct access to modify or want to write the code later separated from the original source. Now check this one which does a similar thing as the above – especially check the new MyFullName delegate and the two new different ways of implementation:

using System;
using System.Collections.Generic;
using System.Text;

namespace DelegateSampling
{
public delegate double MyCustomMath(int[] examScores);
public delegate string MyFullName(Student s);

        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int[] Scores { get; set; }
    }

class Sample
{

            // inline method implementation - anonymous method
            MyFullName lastFirst = delegate(Student s)
                { return String.Format("{0}, {1}", s.LastName, s.FirstName); };

            // inline method implementation - anonymous method
            MyCustomMath avg = delegate(int[] scores)
            {
                int total = 0;
                for (int i = 0; i < scores.Length; i++)
                {
                    total += scores[i];
                }
                return total / (double)scores.Length;
            };

// inline method implementation - lambda expression
MyFullName firstLast = oStudent => String.Format("{0} {1}", oStudent.FirstName, oStudent.LastName);

            foreach (Student s in pupils)
            {
                Console.WriteLine("{0} is {1} - Avg: {2:F2}",
                    lastFirst(s),
                    firstLast(s),
                    avg(s.Scores));
            }
        }
    }
}

It gets interesting now. We have some inline “method block”s within a single method! Those are called “Anonymous Methods” which were introduced in C# 2.0. In C# 3.0 another one came in, the “Lambda Expressions”.

Lambda expressions are still anonymous methods providing a more compact and functional syntax to implement methods inline. In our sample MyFullName firstLast we have a Lambda Expression that takes a single input parameter of type ‘Student’ and returns a string combining first and last names of given student (input and parameter types are defined as Student and string in delegate definition – remember delegate is a method signature). In our sample we get a single parameter. Suppose instead we would get 2 parameters, a Student and an integer representing a desired scores count, and test if student got enough exams. It would look like:

public delegate bool MyFilter(Student s, int i);
// …
MyFilter f = (s,i) => s.scores.Length >= i ;

Can you feel the power! Now like in math we have some sort of f(x,y) to be used as a filter function. With generics -we wouldn’t always get (Student, integer) and return a boolean- a function delegate that takes two parametes as inputs and returns a value could be defined as:

public delegate TResult Func<Tinput1,Tinput2,TResult>(TInput1 p1, TInput2 p2);

for single input and result it would look like:

public delegate TResult Func<Tinput1,TResult>(TInput1 p1);

Since we would need such delegates often C# has already defined delegates that take up to 4 parameters as input and return a value (all named “Func”). That liberates us from declaring a delegate first. IOW you could remove

public delegate string MyFullName(Student s);

and slightly change calling codes to:

Func<Student,string> lastFirst = delegate(Student s)
{ return String.Format("{0}, {1}", s.LastName, s.FirstName); };

Func<Student,string> firstLast = oStudent => String.Format("{0} {1}", oStudent.FirstName, oStudent.LastName);

Great, isn’t it :) Removing delegate declarations, writing our inplementations as lambda expressions, refactoring and arranging placement of classes here is our new version of the above code (removed 2 different ways of fullname implemenation), Code also demonstate “statement lambda” which use a statement block to return the average of scores:

using System;
using System.Collections.Generic;
using System.Text;

namespace DelegateSampling
{
    class Sample
    {
        static void Main(string[] args)
        {
            List<Student> pupils = GetPupils();

            // inline method implementation - lambda expression with statement lambda
            Func<int[], double> avg =
                scores =>
                    {
                        int total = 0;
                        for (int i = 0; i < scores.Length; i++)
                        {
                            total += scores[i];
                        }
                        return total / (double)scores.Length;
                    };

            // inline method implementation - lambda expression
            Func<Student,string> firstLast =
                oStudent => String.Format("{0} {1}", oStudent.FirstName, oStudent.LastName);

            foreach (Student s in pupils)
            {
                Console.WriteLine("{0} \t- Avg: {1:F2}",
                    firstLast(s),
                    avg(s.Scores));
            }
        }

        private static List<Student> GetPupils()
        {
            List<Student> pupils = new List<Student>();
            pupils.Add(new Student("John", "Doe", new int[] { 80, 90, 90 }));
            pupils.Add(new Student("Frank", "Smith", new int[] { 70, 80, 85 }));
            pupils.Add(new Student("Chris", "Brown", new int[] { 70, 60, 75 }));
            pupils.Add(new Student("Tim", "Fisher", new int[] { 80, 90, 100 }));
            return pupils;
        }
    }

        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int[] Scores { get; set; }
    }
}

Are we done yet? We are nearly done but not yet. Wouldn’t it be nice if we could “extend” the classes and have some methods already defined in them? Lets extend our averaging method and use that instead:

using System;
using System.Collections.Generic;
using System.Text;

namespace DelegateSampling
{
    using MyExtensions;
    class Sample
    {
        static void Main(string[] args)
        {
            List<Student> pupils = GetPupils();

            // inline method implementation - lambda expression
            Func<Student,string> firstLast =
                oStudent => String.Format("{0} {1}", oStudent.FirstName, oStudent.LastName);

            foreach (Student s in pupils)
            {
                Console.WriteLine("{0} \t- Avg: {1:F2}",
                    firstLast(s),
                  s.Scores.Average());
            }
        }

        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int[] Scores { get; set; }
        public List<string> Statistics { get; set; }
    }
}

namespace MyExtensions
{
    public static class Extensions
    {
        public static double Average(this int[] source)
        {
            double total = 0;
            for (int i = 0; i < source.Length; i++)
            {
                total += source[i];
            }
            return total / source.Length;
        }
    }
}

We have a namespace with a single method that extends the behaviour of integer arrays (for simplicity integer arrays instead of generic numeric enumerables). It is new to C# 3.0 and means that any integer array (using the namespace we wrote) would have an Average() method defined! DelegateSampling namespace imports “MyExtensions” and we removed “avg” lambda implementation. Instead now we simply do “s.Scores.Average()” –s.Scores is an int[] and we extended integer array sources’ behaviour-. Not surprisingly, the System.Linq space already have an extension method named Avg to average series (and other aggregates like Min,Max,Sum … and even Aggregate which lets you create your own aggregation).

In VFP one of the features that existed from day 0 of foxbase, and what developers love, is the automatic inference of types from declaration. ie:

ix = 3
name = “cetin”

and ask VFP the types of those variables, it answers as N and C respectively. At least it bothered me why would I need to explicitly type everything even when it is obvious what it is (remember 1.GetType() demostrating it knows the type since C# 1.0?). Anyway starting with 3.0 C# have the capability infering the type from usage. Now you use the keyword “var”, ie:

var i = 3;
var name = “cetin”;

And most importantly it is the same as writing:

int i = 3;
string name = “cetin”;

You can also initialize arrays like this:

var numbers = new [] {1,2,3};
var family = new [] {“ulku”,”efecan”,”cetin”};

provided you use the variables declared this way only locally. This is called "Implicitly Typed Variables". No doubt implicitly typed variables are cool but there is more.

"Object and Collection Initialzers" make intializing of record like structures and their collections much easier. We could rewrite our Student class as:

public class Student
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int[] Scores { get; set; }
}

and then initialize a single student like this (object initializer):

new Student { FirstName = "John", LastName = "Doe", Scores = new int[] { 80, 90, 90 }};

or alter our GetPupils() method like to initialize a list of students like this (collection initializer):

private static List<Student> GetPupils()
{
    List<Student> pupils = new List<Student> {
    new Student { FirstName = "John", LastName = "Doe",    Scores = new int[] { 80, 90, 90 }},
    new Student { FirstName = "Frank", LastName = "Smith", Scores = new int[] { 70, 80, 85 }},
    new Student { FirstName = "Chris", LastName = "Brown", Scores = new int[] { 70, 60, 75 }},
    new Student { FirstName = "Tim",   LastName = "Fisher", Scores = new int[] { 80, 90, 100 }}
    };
    return pupils;
}

Another feature is similar to inline creation of methods (Anonymous Methods) is the abillity of creating inline types and it is referred to as "Anonymous Types". ie:

Here we create new type(s) w/o declaring a specific type for them. employee1 is a new type created inline having properties named Name,Title and DepatmentId. employee2 have exactly the same signature and is of the same type of employee1. employee3 have the same number properties and their names are same but the order is different, it would be another type (C# automatically names those types as something like:

<>f__AnonymousType0`3[System.String,System.String,System.Int32]
<>f__AnonymousType0`3[System.String,System.String,System.Int32]
<>f__AnonymousType1`3[System.String,System.Int32,System.String]

There are some other new cool features in C# 3.0 (and some more coming up with 4.0) but we are now armed with enough to talk about Linq. There is one important feature about Linq left and that is "Query Keywords" which VFP developers are familiar with in general. I don’t list them here because starting with the next post we are diving into Linq samples:) In those samples we will see tons of them plus we would see how using lambda expressions is not scary but may be more effective.

Since we are going to work on Linq starting from next post please be sure you have downloaded one of the coolest utilities available, the LinqPad. The LinqPad is for free if you don’t enable auto completion. I am not a salesman for Joseph Albahari and actually he doesn’t know me at all, but I strongly suggest that you also activate autocompletion. Neverthless to say I have started to read his book too on ur university Safari subscription and would be one of the books I truely suggest. (I have VFP versions, VS, SQL server and of course LinqPad on my quick launch bar to give an idea how much I respect that).

A bit generic

2009-02-12T04:23:00.001+02:00

C# 2.0 introduced a series of new features and 3.0 added a bunch of others that together make up the infrastructure needed for Linq. We start to explore them now.

You already learned that C# is a strongly typed language and System.Object is the base of all types. We also talked about the generics a bit. Look at this VFP code:

procedure FindMinimum( tuParameter1, tuParameter2 )
    * code checking if they can be compared
    if m.tuParameter1 < m.tuParameter2
           luResult = m.tuParameter1
    else
           luResult = m.tuParameter2
    endif
    return m.luResult
endproc

You can call a code like this with different types:

FindMinimum(“a”,”b”)
FindMinimum(1,2)
FindMinimum( {^2008/1/1}, {^2007/1/1} )

and VFP would be getting 2 values of the same type and returning minimum of those 2 with the same type as the parameters (assuming there weren’t any errors like passing a date and a boolean). Send 2 strings and get back a string, 2 numbers and get back a number and so on. In C# it is not that easy. You have to declare the types:

public int FindMinimum(int p1, int p2)
{
if (p1 < p2)
{
return p1;
}
else
{
return p2
}
}

A C# coder would unlikely write it as verbose as I did (to be clear on what we are doing) and could write it much shorter like:

public int FindMinimum(int p1, int p2) { return (p1 < p2)? p1:p2; }

However it would work for only integers. You can add others:

public string FindMinimum(string p1, string p2) { return (p1 < p2)? p1:p2; }
public double FindMinimum(double p1, double p2) { return (p1 < p2)? p1:p2; }
public DateTime FindMinimum(DateTime p1, DateTime p2) { return (p1 < p2)? p1:p2; }
// ....

Did you notice that now we have a series of FindMinimum() method codes? Name is same (C# is a case sensitive language, FindMinimum() and findMinimum() are two different methods – it is not recommended to differ methods by casing) but the types are different and that makes the “method signature”s different (a method’s access level and modifiers –here public-, return value, method name and method parameters together constitute a Method Signature ). You can find the details here. If you make a call like FindMinimum(1,2) then C# calls the version defined as FindMinimum(int p1, int p2). Calling part is OK but would you write all those methods for a series of types? You may think that you are clever and instead write this version:

public object FindMinimum(object p1, object p2)
{
   // code to check if objects implement IComparable
   // code to check underlying type, if type can be converted etc
   // comparison using the IComparable implementation and return the result
}

I didn’t even attempt to write that complex code here! Explicit versions do not even need extra coding in them. As you can see trying to be clever we couldn’t even write a simple “generic” version of the code (maybe we could if we have insisted to write that way but then it would be scary to test such a code). Assuming we were able to do it and wrote the “generic” version, that would be a poorly performing piece of an application (think what if we need such a comparison to sort thousands of items – generally such a comparison code target sorting and or finding the smallest/largest in a set). Did somebody say generic? Oh yeah C# generics. Let’s rewrite it:

public T FindMinimum<T>(T p1, T p2) where T : IComparable<T>
{ return ( p1.CompareTo( p2 ) == -1 )?p1:p2; }

It is not the best maybe but it is still a cool generic implementation. Here T is a placeholder for the actual types. We have also used a constraint on that type ( where T : IComparable<T> ) and filtered the types to be only those that implement IComparable interface. Interfaces are contracts with no implementation and forces the type that use it to implement. We don’t have something in VFP to compare with “interface” (or maybe there is and I was not aware of it all these years – all I know it had it for activex interface implementation, as in eventhandler code). Interface is just a class without any implementation of its methods. Practically speaking, you define the methods and document what should it get and return (with a definition of what that return value mean) but do not write the body. Any class that “inherit” that interface must explicitly implement all of its methods. IComparable<T> interface has a single CompareTo(T) member function that accepts an object of type T and returns an integer (0 if this = T, –1 if this < T and 1 if this > T – this as VFP’s this to mean this instance). We call our new FindMinimum<T>(T p1, T p2) like this:

int iResult = FindMinimum<int>(1,2);
string sResult = FindMinimum<string>(“a”,”b”);
DateTime dResult = FindMinimum<DateTime>( new DateTime(2008,1,1), new DateTime(2003,1,1) );

Generic types methods are different from the previous example (or attempt) of creating a generalized version. With “generalized” one there are a lot of things going on, at least up/down casting of the types from/to object (an operation called boxing/unboxing is done behind the scenes, don’t get confused with it but know that it is an expensive operation). With “generic” implementation we are writing a single FindMinimum() but we do help the compiler by explicitly telling what the type(s) would be in the calls we use and compiler creates multiple versions of the FindMinimum() for those types (in our sample compiler would create 3 FindMinimum() with int, string and DateTime types).

You can check generics fundementals FAQ here and the details of generics here. We will continue with these features in next post.

Collections of columns, rows, classes make up the data

2009-02-10T03:59:00.001+02:00

Last time we were talking about types and ended with a fictitious Customer type. In general that is how you work with data in .Net. A row become a type (remember we call Class and Struct "type”) where fields are its properties and a collection of those objects constitute a “table”. We akso talked about arrays but not collections. Collection classes are numerous in .Net. You can see the classes in System.Collections here and each have some specific duty. Within the collection classes most widely known and used one is the ArrayList class. You can think of an ArrayList as an ‘array’ or collection in VFP where you can add/remove new “object”s. ArrayList sounds to be a good flexible collection class that holds whatever you throw at it. If you look its members they are “object”s or to say it otherwise System.Object instances. That is very similar to VFP arrays where you are not enforced to use the same type for its members. That is not good from a performance point but more importantly you would have trouble using such a class to store your structured data (C# is a strongly typed language and compiler checks your types for consistency, however in the case of “object” type the compiler functionality would diminish. You could have an ArrayList for Customer type with no guarantee that an Employee type wouldn’t sneak in).

.Net generics that came with 2.0 was a solution to “typed” collections (generics is not only about collections). You can see System.Collection.Generic namespace members here. The advantage of generic collections is that you specify what the type of its members would be. ie:

List<string> names = new List<string>();
names.Add(“cetin”);
names.Add(“joe”);
List<Customer> customers = new List<Customer>();
Customer X = new Customer(); // with constructor parameters if any
Customer Y = new Customer(); // with constructor parameters if any
X.customerID = “MSFT”;
Y.customerID = “ORCL”;
customers.Add( X );
customers.Add( Y );

You can’t add say an integer or Customer to “names” list. You can’t add a string to “customers” list. Generic lists have some usefull methods that are not found in non-generic collections. Arrays and Collections are sequences and sequences are an important part of data operations (anywhere it is the case, in VFP you can think of the sequence as a group of records, in SQL server a set of data and so on – well like saying the same thing with different sentences). In the future we will see tons of code related to collections and some specifics about them as needed. But for now we need to know another aspect of data in .Net, the core “data” processing classes.

In the heart of data management with .Net there is (or there were from an aspect) ADO.Net and its classes . Doing an analogy with VFP in a gross scale, a database is represented with “DataSet” class, table is represented with “DataTable” and relations with “DataRelation”. There are multiple tables and multiple relations, DataTableCollection and DataRelationCollection classes host them in the DataSet. Within a DataTable records are represented wih DataRowCollection (collection of DataRow class), columns with DataColumnCollection (collection of DataColumn class) and ConstraintCollection (collection of Constraint class). Each of these are classes and have their own set of PEM. Developers used typed and untyped datasets within ADO.Net. Untyped sets were those that do not have predefined classes of its tables, columns and so on but generically created at runtime (as if you do a browse with a table that user chooses at runtime – browsing is easy but you can only do few generic operations on such data). Typed datasets are generated by VS tools from existing data such as SQL server database. You generally generate and do not touch the generated classes (they are like scx, vcx … files if you modify them outside designer they would be overwritten). You can find books dedicated solely to ADO.Net and its classes and how to do data management with ADO.Net in .Net. I won’t go into ADO.Net details but give some samples later. The reason I am not dealing with its details, while it is in the heart of .Net data operations, is because now it has already evolved and C# 3.0 and .Net framework 3.5 made it somewhat obsolete (sorry if obsolete is not the right word, what I mean you may do perfectly valid and effective data processing and yet don’t even be aware of that you used one those ADO.Net classes). On the other hand, it may be unfair skipping the painfull story of .Net data processing before Linq, I don’t know. Anyway we would see how it goes when we get into Linq and coding for data.

(A side note, this might be a llittle strange but if you check the books about C#.Net you would see that in all those books the space allocated to working with data is less than 1/10th. However it is the Linq that encourages me to talk about data processing in .Net. Since Linq came out you can find books that just talks about data processing with Linq ).

Typing is important

2009-02-07T05:34:00.001+02:00

Let’s start with some terminology (I will however explicitly skip some details that I think there is no practical importance to a beginner and wouldn’t be much loss if you never learn).

In VFP we have a some “type”s like C for character, N for numeric, D for Date … and O for object. We generally don’t realize that some of these are complex or compund types with the exception of object. To use all are simple types and we call all classes as of type “O - Object”.

In .Net, remember, everything is an object. All those objects’ class are TYPEs -rather than simply saying it is an object a type refers to its ‘class’ is (however read on though saying class here it doesn’t need to be a class but a struct, you would see shortly). To give some samples, a Type can be Int32, String, DateTime, DataGrid, DataSet, DataTable, Form, IEnumerable, ISerializable … In other words, in .Net you would see Type a lot and it refers to a class or struct ( it is very important to know what an ‘object’ type is and not surprisingly the base class of all objects “System.Object” have a method GetType – remember 1.GetType() ? And also there is a System.Type class).

Types in a gross scale are classified into Value and Reference types ( we are not saying Class but Type ). Value types are “Structure” and Reference types are “Class”. There are a few slight differences between Structure and Class, if I tell them all you would get confused, here is what matters for you in a practical but unoffical way:

In VFP you declare a class named “test” like this (In VFP we need to specify a base class type, just to fullfill that let’s say this one is custom):

define class test as custom
    SomeProperty = “An initial value”
    AnotherProperty = 1

   Procedure init
        * blah blah
endproc
enddefine

In .Net if it is a Class (similar to VFP’s protected,public,hidden in .Net access modifiers exists -private,public,internal,protected- and used often but not used in this code for simplicity – and in fact “property” in this sample is a misnomer):

class Test
{
     string SomeProperty = “An initial value”;
     int AnotherProperty = 1;

    public Test()
    {
       // blah blah
    }
    public int SomeMethod() { // … }
}

was possible. If it were a structure it was not,because:

A structure cannot initialize its “field”s at their point of declaration. A structure cannot have a “default constructor” ( a default constructor is a parameterless constructor –what we know as init method- ) A structure must initialize all of its fields in its constructor methods.

and it would look like this instead:

struct Test
{
    public Test( string someProperty, int another )
    {
       this.SomeProperty = someProperty;
       this.AnotherProperty = another;
    }
    public int SomeMethod() { // … }
}

Note that there is no method named “init” there. A method that has the same name as the class/struct is the constructor –init- method. A constructor doesn’t have a return type either.

Also note that SomeProperty and AnotherProperty are not properties (as we know in VFP) but “fields” of the type ( once again to remind, type means class or struct ). A “Field” is what we know as a “property”. A “property” on the other hand have get and/or set methods (like access and assign methods). However, you are lucky in 3.0 and later you don’t need the declaration of a field and then implementation of a property with get/set – of course you can if you want to. Now it’s like how it is in VFP and simply looks like:

public string MyProperty {get;set;}

Structures are Value types and typical samples are int (an alias for System.Int32 structure), DateTime, Boolean, Void, Nullable(T) … - all built-in C# types are aliases to System.X where X is a structure (except string, which is a class). You would often use such types: int, uint, byte, object, bool … for example int is an alias to System.Int32 while byte, short, long are aliases to System.Byte, System.Int16, and System.Int64 structures respectively (signed).

Classes are Reference types and mst of the types in the .Net are class, such as string (alias to System.String class), DataTable, Array, Type …

When passing them around, as you should have already guessed from VFP’s by value/by reference, value types are passed as copies, while reference types are passed as pointers to object. Value types live on the stack and just like in VFP when a “value type” variable goes of scope it is freed from stack (a good anology of stack is plates in a restaurant, new dish gets on top and the last dish is always the first to leave ‘stack’). Class types (reference IOW) on the other hand live on a location called heap and when an “object” goes out of scope the space it allocates in memory isn’t immediately released. A special garbage collection system is always on duty and reclaims the memory but need not be immediately. How this effect you? You shuld of course live w/o knowing this differences but it is always good to know especially when it may effect performance. Simple lightweight types are good for Structures, there is little to copy on stack when passing them around. If it is a large object then either having it as a class or passing by reference (ref and out keywords exists to do that) is the documented recommendation (and for purists passing by reference is a security matter).

A row from Customer table can be thought as an object derived from a “Customer” class. Fields of table then become the properties of Customer. ie:

public class Customer
{
    public string CustomerId {get; set;}
    public string CompanyName {get; set;}
    public string ContactName {get;set;}
    public DateTime? LastUpdated {get; set;} // ? stands for nullable

    // since this is a class it can have PEM
    // such as other fields and properties that are not data columns
    // calculations, events

// just to honor sampling consider ContactName exists as a column
// but there is no ContactFirst, ContactLast columns ( or imagine employee First,Last exist but not FullName )

    // for simplicity no error check and assumed single word firstName like “Joe Doe”
    public string ContactFirst {
         get { return ContactName.Trim().Split( new char[] { ' ' } )[0]; }
         set { ContactName = ContactName.Replace( ContactName.Trim().Split( new char[] { ' ' } )[0], value ); } }

    public string ContactLast {
         get { return ContactName.Trim().Split( new char[] { ' ' } )[1]; }
         set { ContactName = ContactName.Replace( ContactName.Trim().Split( new char[] { ' ' } )[1], value ); } }

    public double Balance()
    {
         // ….
         // return …
    }
    //List<Order> Orders = ...
    public double OrdersTotal()
    {
    //   return this.Orders.Sum( o => o.Quantity * o.Price );
    }
}

Thinking a row as an object is not something we are accustomed to in VFP. The languge is object oriented but relational databases are not suited well to think in object terms. I think however you can see (with the knowledge of classes and access/assign methods from VFP) the power of looking a row as an object. It can have navigational “graph” to its child rows and parent rows ( child Orders or example ), have customizable methods directly on the class etc. A collection of this Customer type would constitute a “Customer Table”. This would then lead to new queries that we are not accustomed to yet but much more eaiser and powerfull sometimes. Such type of queies could be called OQL queries (Object Query Language).

Before closing today’s post I would to add that Customer above is a user defined “type”. You would create a “new” Customer like this:

Customer c = new Customer(); // like scatter name or append/inert into buffered table
c.CustomerId = “CSHARP”; // like replace
//…

Can you count it?

2009-02-04T23:33:00.000+02:00

Today before we go on let me clarify a point with a sample code. I got a feedback claiming that you can't do things like:

1.ToString() or 1.GetType() in C#. I accept that it might be confusing to attach a method to a literal like 1 but you can do it. You can even do that with 1U, 1F, 1.0,1m etc. U, F,m are type specifier suffix (U means unsigned integer, F float, D double, m Decimal). Here is a sample code at the risk of confusing more:

using System;

class YouCanDoThatIfYouBelieve
{
   static void Main()
   {
     Console.WriteLine( 1.ToString() );
     Console.WriteLine( 1.GetType().ToString() );
     Console.WriteLine( 1.GetType().GetField("MinValue").GetRawConstantValue().ToString() );
     Console.WriteLine( 1.GetType().GetField("MaxValue").GetRawConstantValue().ToString() );

     Console.WriteLine(
        1U.GetType().GetField("MinValue").GetRawConstantValue().ToString() );
     Console.WriteLine(
        1U.GetType().GetField("MaxValue").GetRawConstantValue().ToString() );

     Console.WriteLine( 1.0.GetType().ToString() );
     Console.WriteLine( 1.0.GetType().ToString() );

     Console.WriteLine(
        1F.GetType().GetField("MinValue").GetRawConstantValue().ToString() );
     Console.WriteLine(
        1F.GetType().GetField("MaxValue").GetRawConstantValue().ToString() );

     Console.WriteLine(
        1D.GetType().GetField("MinValue").GetRawConstantValue().ToString() );
     Console.WriteLine(
        1D.GetType().GetField("MaxValue").GetRawConstantValue().ToString() );

     Console.WriteLine( 1m.GetType().ToString() );
     Console.WriteLine( 1m.GetType().ToString() );
   }
}

Though it works like this probably you should believe in "you can't do that" phrase and go with the verbose mode like:

int number = 1;
string myString = number.ToString();

Back on track. Today I want to talk about Arrays (and maybe more if time and space permits).

Arrays in C# is on steroids (squared steroids) compared to VFP arrays. You can create single or multi dimensional arrays (multi dimensional means multiple dimensions really -in VFP we can only use up to 2 dimensions- but managing multiple dimension arrays should be hard).
Here are some differences from VFP array first:
All elements of a C# array are of the same type.
The size is specified during declaration (either implicitly or explicitly).
Arrays are zero based (first element starts at 0 unlike 1 in VFP)
An array could have 0 elements (yet another confusion, but don't worry)

This code from MSDN library shows different types of arrays and how to initialize them:

// Single-dimensional array (numbers).
int[] n1 = new int[4] {2, 4, 6, 8};
int[] n2 = new int[] {2, 4, 6, 8};
int[] n3 = {2, 4, 6, 8};

// Single-dimensional array (strings).
string[] s1 = new string[3] {"John", "Paul", "Mary"};
string[] s2 = new string[] {"John", "Paul", "Mary"};
string[] s3 = {"John", "Paul", "Mary"};

// Multidimensional array.
int[,] n4 = new int[3, 2] { {1, 2}, {3, 4}, {5, 6} };
int[,] n5 = new int[,] { {1, 2}, {3, 4}, {5, 6} };
int[,] n6 = { {1, 2}, {3, 4}, {5, 6} };

// Jagged array.
int[][] n7 = new int[2][] { new int[] {2,4,6}, new int[] {1,3,5,7,9} };
int[][] n8 = new int[][] { new int[] {2,4,6}, new int[] {1,3,5,7,9} };
int[][] n9 = { new int[] {2,4,6}, new int[] {1,3,5,7,9} };

In the sample above, jagged arrays initialization make it a little confusing. If you look closely it is like array of arrays where each array member can have different Length (element count) arrays - n9 have 2 'arrays' one with 3 elements and the other 5.
On the other hand, two dimensional array shown as a sample for multidimension arrays, are referred to as "rectangular arrays" - they are like VFP's 2D arrays with fixed size rows and columns.
You can see how easy it is to initialize an array let's see a tip (something that we can't do in VFP):

You can split a string into tokens using String class' Split() method (think of alines() in VFP). One overload of Split() expects an array for chars. You could do this:

string someString = "One, two, three and four. Parse into tokens.";
char[] delimiters = new char[] {',','.',' '};
string[] tokens = someString.Split(delimiters)

Nothing special here but look at this one doing the same thing:

string someString = "One, two, three and four. Parse into tokens.";
string[] tokens = someString.Split(new char[] {',','.',' '});

The ability to declare and initialize an array at the location where it is expected as a parameter is cool compared to VFP IMHO (using new ... where a parameter is expected is not limited to arrays). Among C# developers this is a very simple thing that they wouldn't think of it as a 'tip' but I thought this detail would escape from many VFP developers (since we don't expect a capability like that). Related with this let me add this:
A string is an array of chars and string class has an "indexer" to its chars (an indexer is a special type of method that accepts a parameter(s) and returns one of its members - like grid.Columns[i], excelSpreadSheet.Cells(1,1), integer.bits[5], collection.Item["key"]) you can do this:

string test= "My String";
char myChar = test[1]

Keeping in mind that C# is zero based, this would return 'y' as a char (a char is a unicode value and this might not be the correct way to get the 2nd character of a string but in practice it works like this). In the same vein since a string is an array of chars you can create a new string from an array of chars by simply creating a string object (did I ever say new is like VFP's createobject() and used to create an object) passing the array to its 'constructor' method (constructor is what we know as Init).

char[] charArray = {'a','b','c'};
string concatted = new string(charArray);
// concatted is now a string - "abc"

I hope you are seeing the power behind this seemingly very simple (for .Net) things.

Yes, of course I didn't show anything that you can't do in VFP but it is much easier in .Net, isn't it?

(BTW I told you that array is initialized with a known size - that is true but you can use "Array" class and Array.Resize( myArray, newSize ) - it is not an efficient operation because since an array have a predefined size, this method does the resizing by creating a new one and then replacing with the old)

Arrays are important in any language and in .Net it is someting that you can 'count' its elements. If you can count it then it means you can iterate its elements or in other words you can enumerate it as if you are giving record numbers and that should ring some bells:) In VFP we could do SQL:

select ... from ... where ...

and the 'Query' source is a table (or anything like that, a cursor). C# has gained "Language INtegrated Query" aka LINQ capability, it can do "SQL" now (well not SQL but SQLlike). Interestingly though the source need not be a table, it can be anything that you can get an enumerator on or to say it otherwise anything you can count:)

Hopefully we will complete some more stuff this week and then "Let the fun begin" with Linq (I really became an addict of Linq and F# I think but who wants to have medication for it:) Just to keep your appetite alive, here are samples to some 'countable' things (no need to confuse you with IEnumerable, I... yet) that you can do "Query" operations on:

Arrays, strings, any type collections (for example directories and files, running applications and processes), XML documents, any text file (it has lines, characters etc that you can count) ... and why not, data files:)

Do you remember this?

var q = "Count distinct characters in this string".Distinct();

If should now make a little more sense.

See you on next post.

Welcome back.

2009-02-04T04:25:00.001+02:00

Yesterday we made a head start to .Net bypassing all the basics and going directly into Linq (what C# 3.0 was about). Afterall, at least for sometime, this blog series target VFP developers who already have an idea about basic programming and curious about how would they do the data centric operations that they do everyday. Today however I think we should go into some basics at least about the syntax and a few more.

While reading, please keep in mind I prefer not to use a formal language and strict formal definitions. That bores me in the the first place and wouldn’t help to keep the reader awake :) If you are purist then you can read the official C# Language Specification. Instead I prefer a relaxed “toy with it” approach.

You have probably heard it already, everything in .Net is an object. Huh? Everything? Yes, everything. What does everything mean then? Just what it says, literally anything. In VFP:

number = 1

oForm = createobject(“form”)

there is only one object (object variable) in this code and that is ‘oForm’. Now for a second think of it were a C# or VB code (assuming the syntax was right). Then there would be 4 objects shown (those are in bold in the code). How can that be? Here it goes, you can find it playing a QA game:

What is 1? A number. Right, but can you classify it a bit more? An integer. Very true:) So 1 is an “object” subclassed from “integer class”.
What is “form”? A string. Right on target, ”form” is an “object” from “string class”.

number and oForm are object variables of integer and form ‘type’ respectively (but later you would see that number and oForm should have their class –referred to as TYPE in .Net- specified in C#).

If they are objects then they should have something like a method and yes they have. All objects’ base class is ‘object class’ and to be more precise System.Object class. Object Class have some methods like ToString(), GetHashCode() … and interestingly you can use these methods on 1 and “form”.

1.ToString()
1.GetType()
”form”.GetHashCode()

since “form” is a string instance we could use a string method like this too:

“form”.ToUpper()

OK I assume you are now convinced that they are objects (all TYPEs –read it classes to understand- are derived from object and this is referred to as Common Type System, aka CTS).

Notice that ToString() and GetHashCode() are “attached”. In VFP if we needed to convert a variable named luVar to a string, get its substr(…,2,3), then uppercase it and trim:

alltrim( upper( substr( transform( luVar ),2,3 ) ) )

luVar might be luVar variable or 1, “form” etc and in C# we do:

luVar.ToString().Substring(1,3).ToUpper().Trim()

since method names are explanatory you can see the pattern, methods are attached and chained. You should also note something important here for C# – C# is a case sensitive language and counting starts from 0:

ToString is the right method name, you can’t write it like Tostring, toString, tostring…
substr( “astring”, 2, 3) or anArray[2] in VFP means start from 2nd character or 2nd member respectively.
In C# it means 3rd (0 based). Thus a typical for loop in VFP looks like:

for i = 1 to 10

while in C# it is:

for(int i=0; i < 10; i++)

OK now lets continue. In VFP consider we have a code like:

Procedure SomeProcedure( tcString As String, tnStart As Int, tnEnd As Int) As String
Local ix As Int, result As String
result = ""
For ix = m.tnStart To m.tnEnd
  result = < Something >
  local jx as int
  jx = …
Endfor
result = < Some more operation here >
if ( result = < Something > )
  * …
Endif
TEXT to lcText noshow
I want these lines to appear as I wrote.
Line 2
Line 3
ENDTEXT
* …
result = Textmerge("On Date: << Date() >>:") + Chr(13)+Chr(10)+;
         m.result + ;
         " Some Text "
Return m.result
Endproc

In C# syntax:

string SomeProcedure( string tcString, int tnStart, int tnEnd)
{
   string result = "";
   for( int ix = m.tnStart; ix < m.tnEnd; ix++ )
   {
      result = < Something > ;
      int jx = …
   }
   result = < Something > ;
   if ( result == < Something > )
   {
      // …
   }
   string lcText = @"I want these lines to appear as I wrote.
Line 2
Line 3"

// …

result = String.Format(“On Date: {0}:{1}”,DateTime.Today.ToString(“d”),Environment.NewLine) +
    result +
    " Some Text " ;

Return result;
}

Note the differences:

VFP	C#
Parameter, return and variable types are optional Type of variables are not specified before they are used (specification is only for intellisense support when you use)	Parameter, return and variable types are mandatory TYPE (class) of variables are specified before they are used (strongly typed language)
There are different block specifiers like procedure …endproc, for … endfor	Code blocks are specified with curly braces { }.
Local variable declaration makes the variable to be scoped to the whole procedure from the point it is declared.	A local variable is scoped to the block it is declared in (practically scoped to its outer curly braces { } ). For example: In the code above both ix and jx variables are scoped to the for loop. If later in the code you were to use ix you need to decalre it.
	@” … “ construct is a special version of “ … ” that treates the string like text…endtext of VFP
Equality operator (=) is both used to assign and test equality	= (single) is used for assignment and == (double) is used for testing
[ENTER] ends a command line Semicolon is used to continue on next line	Semicolon ends a statement (command line) [ENTER] is used to continue on next line

To recap, continuation example:

string result = aStringVariable.ToString().Substring(1,3).ToUpper().Trim();

string result =
     aStringVariable.ToString()
                    .Substring(1,3)
                    .ToUpper()
                    .Trim();

are equally valid syntax.

I think it is enough for today folks, soon we will end the boring parts and have some fun.

New Horizons

2009-02-03T03:17:00.001+02:00

VFP has always been good to me and it is what I use to earn my life. However it is time to move on to more promising languages. I, like many other VFP developers, started my move little by little to .Net (and chose C#/F# as my .Net language(s)).

Why would I move? VFP is easy, very good at data processing and I can do whatever I need with it. Well I wouldn't argue how good VFP is. It is good, right but not as good as to be at work in a few years (mostly because we would see 64 bits OS in use widely - I would say in a few months). Your codes would work or you could continue to create new VFP applications, even after year 2015 as long as you and your customers can live with 32 bits. If you don't see it as a threat, then still there are reasons for you if you answer yes to a few of these questions:

Want to create richer UI? Saying richer, I mean it. A UI that you have never experienced in VFP with WPF. WPF - Windows Presentation Foundation which is a set of APIs with 2/3D graphics engine gives you the ability to create user interfaces where the only limit is your imagination.
Want to create web applications besides desktop applications or a hybrid of them?
Want to create distributed applications?
Want to create applications for mobile devices?
Want distribution of updates to your software to be controlled by proven technologies w/o requiring you to write code?
Want to parse and/or compose XML documents using classes that knows XML well?
Want to send email, create network connections, check credentials, authenticate logins, ftp etc, in other words do network related operations without a need to use hard to use win32APIs, activex etc tools?
Want to draw charts, text, vector graphics to devices? Again using the classes that are at your disposal w/o win32APIs and activex.
Want to have the ability to write your code and compile it when you are at your customer's site and all you have is a newly installed windows XP or later?
Want to write applications that work on multiple threads, multiple processors, multiple monitors ... multiple platforms?

And the list would go on ... "OK but as a VFP developer I have questions" you say.

Q: If I decide to work with .Net which language should I choose? VB or C#?
A: There is no solid answer to that question (I don't have one and haven't read one at any site yet). You may choose any .Net language you want and .Net languages are not limited to VB and C#. You can see a list of them here and here. It is a shame anything I know and heard of (and those I have never heard before) are on that list while VFP is not
I chose C# (and F#). Whatever you choose wouldn't matter much because you would be using .Net classes and (if you take out language specific syntax) most of the code that you write would be same - it is not the case always:) Anyway check documentation page for example. You would see code written in different .Net languages look very similar. Visual Studio 2008 have C#, VB an C++ (2005 also had J#) but it is extensible and different .Net languages have IDE created for them. For C# there are other IDE too like SharpDevelop. In VS2010 F# will be included (I am not sure about IronPython and IronRuby).

Q: I work with VFP because its language is easy. What about .Net? Wouldn't I have a steep learning curve?
A: If being easy is a concern, you can think of using VB.Net. Many developers claim that VB is an easy language and targeted to Rapid Application Development RAD. Easy, on the other hand, is a relative term. To me C# is easy (and F# probably easier - don't know if I feel like that because I started it after C# or because I have worked with a similar language years ago).

Q: What is important to me is data processing. Is that easy and fast like it is with VFP?
A: Short answer is yes. On the long run it is even better. During .Net 1.1 days data munging in .Net was awfull (and I have published some demo code both in C# and VFP against SQL server data to demonstrate VFP was exceptionally faster on UniversalThread). However with 2.0 it got faster. Linq (later on this LanguageINtegratedQuery) gave a boost and each and everyday it is getting much better. VFP developers, generally think about local VFP native data.

Q: But .Net doesn't have a native data backend.
A: Yes, it doesn't. But just like you could use any data sources in VFP you could use them in .Net and doing that in .Net has got easier. In .Net you have a greater range of datasources.

Q: What would be the initial cost I need to make while I am learning and have nothing to sell yet?
A: Zero.

OK if I couldn't sell you .Net today I can do it in the upcoming blog entries. For those that I could, lets see some action:)

What do you need to start with? Ideal environment is Visual Studio full blown version but you can start with one of the Express versions.
All the tools you need initially have 'Express' versions. 'Express' denote lightweight version of X in .Net world. Besides languages and IDEs, you would find Express versions of popular databases like SQL server, Oracle. Also check beginner developer center. Before we move on, I strongly suggest one of the coolest downloads you can find and that is LinqPad.

I assume you chose C# and have at least C# express. Let's kick off.

Every programming starts with the famous "Hello World" application. If you want, you can create that masterpiece and watch it for hours later but for now lets do a little more basic. VFP reminds data so lets start with a LINQ sample and simulate a scan ... endscan. Go and launch C# express (or VS if you have it). Before we continue, if you are like me, you would want to create temporary test codes and then either save or discard them. In .Net it is called "Temporary Projects" and you need to enable it. Go to tools\Options and select "Projects and Solutions" (check Show all settings), be sure "Save new projects when created" is not checked.

OK we are ready, lets Query We start with a simple "Console Application". Console Application is what we know as DOS application.

If you opt to choose using "Temporary Projects" don't even bother to change its name. Since we directly dived into the heart of .Net w/o the wonderfull "Hello World" and other basics this might be hard to catch at first but don't bother, just do for now. We are simply toying with it:

Go to Data\Add new Datasource and select 'Database'. Add a connection to SQL Server Northwind database and add all tables in it (if you don't have SQL Server you can download express version and if you don't have northwind sample database you can download it from here).
Right click project and choose "Add new item"
Add Linq To SQL class (since the release of EF Linq To EF supersedes Linq To SQL - details here and you can download Entity Framework here). I won't debate on usefullness of Linq To SQL (referred as L2S) today. IMHO it is still has value, at least in understanding Linq syntax if nothing else.

Click "Database Explorer", expand Northwind database and drag & drop Customers, Orders, Order Details and Employee tables (and other if you wish) onto the design surface as shown below:

Note that I started L2S file creation with Nwind.dbml name and it gave the NWindDataContext name to the newly created "DataContext" (DataContext is a class - skip details now).

OK now double click "Program.cs" and arrnage the code so it reads:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
NwindDataContext db = new NwindDataContext();
var queryCustomers =
from c in db.Customers
where c.Orders.Any(o =>
o.OrderDate.HasValue &&
o.OrderDate.Value.Year == 1997)
select c;

foreach (var cus in queryCustomers)
{
Console.WriteLine("Customer {0} made {1} orders. First:{2} Last: {3}",
cus.CustomerID,
cus.Orders.Count,
cus.Orders.Min(o => o.OrderDate).Value,
cus.Orders.Max(o => o.OrderDate).Value);
}
}
}
}

Copy & paste the code if you are lazy:) But I strongly suggest you type it so you would see how you get Intellisense support. To run it press [Ctrl+F5] or from menu
"Debug\Start without debugging".

A very important note here: This query is not meant to be an efficient one. You would see why as you progress and/or monitor what commands are executing against SQL server (for now using SQL profiler) and when.

Tip:
var queryCustomers =
from c in db.Customers
where c.Orders.Any(o =>
o.OrderDate.HasValue &&
o.OrderDate.Value.Year == 1997)
select c;

is like

text to lcSQL noshow
select .... where .... =?..
endtext

in a sense. It is not executed until results are asked in any way like an SQLExec() (here with foreach() which simulate a Scan...EndScan). And just like SQLExec() it is called again against to the backend (but unlike SQLExec() or RV requery() it has some controlling options). It is too much for a start IMHO. What you should note anayway is that, there is some SQL like code in C# code. That is "LanguageINtegratedQuery" that gets executed uniformly independant of the underlying datasource (in VFP that is CursorAdapter that can come closest but still much far away from it - in fact I shouldn't even try to make a correlation). And folks this is only the tip of the iceberg with Linq! Just for fun following line is also a Linq expressions:

// Count distinct characters in a string
var q = "Count distinct characters in this string".Distinct();

Or this one:
// DBF files under VFP home folder and its subfolders
// Note: This one gets simple string list (oh well array for purists)
// using a slightly different variation we could return an array of "file information" objects
var dbfFiles =

from

f in Directory.GetFiles(@"C:\Program Files\Microsoft Visual FoxPro 9",
"*.dbf",
SearchOption.AllDirectories)
select f;

To kill your curiosity and test some code easier, see extra code generations etc do not forget to download the COOL LinqPad. If you pay few bucks you can activate the autocompetion and of course the book itself is a breeze IMHO (finally my dream came true and now I have ability to reach safari and I can read it - BTW there are literally hundreds and maybe thousands of other good resources).

Oh well moer than enough I think for the first blog entry Till next time you can check out this video - Introduction to C# Express.