Search

Categories

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Send mail to the author(s) E-mail

# Friday, 17 October 2014
( Expression | Func | Linq )

Linq provider with translate linq to SQL (for EF)

Expressions wrap a Func type.

static void Main() { var db = new MovieDB(); IEnumerable<Movie> query = // Lambda expression being passed in - how to filter db.Movies.Where(m => m.Title.StartsWith("Wal")) // Lambda expression - how to filter .OrderBy(m => m.ReleaseDate.Year); foreach (var movie in query) { Console.WriteLine(movie.Title); }
Then look at profiler:

SELECT [Project1].[ID] AS [ID], [Project1].[Title] AS [Title], [Project1].[ReleaseDate] AS [ReleaseDate] FROM ( SELECT DATEPART (year, [Extent1].[ReleaseDate]) AS [C1], [Extent1].[ID] AS [ID], [Extent1].[Title] AS [Title], [Extent1].[ReleaseDate] AS [ReleaseDate] FROM [dbo].[Movies] AS [Extent1] WHERE [Extent1].[Title] LIKE N'Wal%' ) AS [Project1] ORDER BY [Project1].[C1] ASC

Outer query for ordering.  Sub query for filtering – StartsWith “Wal”

How do these Func’s get translated into Expressions that go into SQL Server?

image

Ahh, not selected the extension method for IEnumerable.

Using IQueryable (which derives from IEnumerable).  But all remote linq providers implmement properties which are IQueryable.

No longer take a Func<T>, but an Expression<T>.  Where T is a Func<T>

image

Opaque – only tells us it takes an int, and returns an int.  Doesn’t really see what's inside of that method.

Expression – essentially when the extension method for IQueryable is doing.

write(square(add(1, 3)));

Invoking the square Func no longer works. (method delegate or event is expected)

image

In immediate window:

image

image

NodeType is multiple, left hand side is x, right hand side is x.  Value to produce is Int32.

Compiler doesn’t compile lambda expression to IL, but builds a data structure – an abstract syntax tree, that have objects inside that represent the operations that we have expressed in code.

Up to linq provider to take the data structure and figure out how to translate it.

Comprehension Query Syntax

var query2 = from m in db.Movies where m.Title.StartsWith("L") select m;

C# compiler translates this syntax into extension method calls.

var q2 = db.Movies.Where(x => x.Title.StartsWith("L")) .OrderBy(x => x) .Select(x => x);

Many of these LINQ oriented features are useful for everyday code

  • Functional programming
  • Reduced noise
| | # 
# Wednesday, 26 June 2013
( Linq | LINQPad )

http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

Use LINQPad

Any()

// Countries which don't have Any people
var counte = Countries.Where(c => !c.Persons.Any());.Dump();

Do Any of the arguments satisfy the condition?

string[] words = { "bob", "dave", "bill", "save" };

// returns true
words.Any(w => w.Contains("av")).Dump();

// returns dave and save
words.Where(w => w.Contains("av")).Dump();

All()

Evaluates the argument against All elements in the list

var people = Persons.Dump();

// Countries where Any of its people have a name with o in it
var countries = Countries.Where(c => c.Persons.Any(
                                                    p => p.Name.Contains("o")
                                                  )
                                )
.Dump();

// Countries where All of its people have a name with o in it
var countr = Countries.Where(c => c.Persons.All(
                                                    p => p.Name.Contains("o")
                                                  )
                                )
.Dump();

image

**GOTCHA**

// Get countries where All the people have a name which contains d
// however this returns the country which has no people
var countrb = Countries.Where(cty => cty.Persons.All(
                                                    p => p.Name.Contains("d")
                                                )
                                    // need to say that the country has people in it
                                    && cty.Persons.Any()
                             );.Dump();

If USA has non people in it, then need Persons.Any to not bring it back.

Select()

// Person where its Countries where All of its people have a name with o in it
var countra = Countries.Where(c => c.Persons.All(
                                                    p => p.Name.Contains("o")
                                                  )
                                )
                       .Select(c => c.Persons )
.Dump();

image

| | # 
# Tuesday, 28 May 2013
( Linq | LINQPad )

Using a block style approach http://stackoverflow.com/questions/2924490/best-ways-to-format-linq-queries

void Main()
{
    var listAccounts = new List<Accounts>();
    
    foreach (var groupAccountLink in GroupAccountLinks)
    {
        var x = Accounts.Where(
                            acc => acc.GroupAccountLinks.Any(
                                                            gal => gal.GroupAccountLinkID == groupAccountLink.GroupAccountLinkID
                                                                   && gal.ReviewItems.Any(
                                                                                        ri => ri.ReviewItemStatusID == 3
                                                                                              && ri.Review.ReviewerID == 1000)
                                                            )
                            && acc.AccountID == 1002 //Charlie
                         ).FirstOrDefault();
        listAccounts.Add(x);
    }

    listAccounts.Dump();
}

Using LINQPad

| | # 
( Linq | LINQPad )
  • Start LINQPad
  • In VS, open Debug -> Attach to Process...
  • Choose LINQPad.exe
  • Set breakpoint in your code called by LINQPad C# code, or write Debugger.Break() in your LINQPad script where you want the debugger to halt.
  • Execute the script from LINQPad and watch the magic.

Can see the code in VS and step through. http://stackoverflow.com/questions/5564369/how-to-debug-linqpad-query-in-visual-studio-debugger

| | # 
# Tuesday, 09 April 2013
( Linq )

Am figuring out an issue where I want:

“Find GALs where there are multiple corresponding RIs, however not where the RI.ReviewID is different”

Using Linqpad like a simple scratchpad is very useful:

Especially if run out of memory, can commit each transaction on the fly:

// get all GroupAccountLinks which have > 1 review item - this is normally as they are in multiple reviews (applicaitons)
var galMulti = GroupAccountLinks.Where(gal => gal.ReviewItems.Count() > 1).ToList();
//var galMulti = GroupAccountLinks.Where(gal => gal.ReviewItems.Count() > 1 && gal.GroupID == 3103); // lim test
// 415,311 records
//var list = new List<string>();
//var listOfRIToDelete = new List<long>();

// iterate through all groupAccountLinks above - which would give applicationID and reviewID and GAL which has multiples
//foreach (var galWithMulti in galMulti.Take(10000))
foreach (var galWithMulti in galMulti)
{
    var riAnon = from r in ReviewItems
         //where r.GroupAccountID == 1452
         where r.GroupAccountID == galWithMulti.GroupAccountID
         && ( r.ReviewItemStatusID == 0 || r.ReviewItemStatusID == 5 )
         group r by r.ReviewID into g
         select new { ReviewID = g.Key, CountOfReviewItemsWhichHaveThisReviewID = g.Count() };
    var onlyReviewIDWhereMoreThan = riAnon.Where (x => x.CountOfReviewItemsWhichHaveThisReviewID > 1 ).ToList();
    
    // add offending ReviewItemIDs to list
    foreach (var element in onlyReviewIDWhereMoreThan)
    {
        //var message = "galID: " + galWithMulti.GroupAccountID.ToString() + element.ToString();
        //list.Add(message);
        
        // have got reviewID and GroupAccountID
        var riToDelete = (from r in ReviewItems
                        where r.GroupAccountID == galWithMulti.GroupAccountID
                        && r.ReviewID == element.ReviewID
                        && r.ReviewItemStatusID == 0
                        select r).First();
        //listOfRIToDelete.Add(riToDelete.ReviewItemID);
        ReviewItems.DeleteObject(riToDelete);
        SaveChanges();
    }
}

//list.Dump();
//listOfRIToDelete.Dump();
| | # 
# Tuesday, 02 April 2013
( Linq )

Introduced in .NET 3.5

Using Linqpad against my .web DLL which includes the EF4 context.  SL project.

  • Implicit joins (so don’t have to explicity say each join)
  • Dot notation (or fluent) as opposed to Query Expression syntax

.Where – from http://www.blackwasp.co.uk/LinqQueries.aspx

var developers = employees.Where(

e => (e.Title.Contains("Developer") && e.Salary > 35000)

|| (!e.Title.Contains("Developer") && e.Salary < 25000));

var developers =

from e in employees

where (e.Title.Contains("Developer") && e.Salary > 35000)

|| (!e.Title.Contains("Developer") && e.Salary < 25000)

select e;

Let – reduce complexity

var developers =

from e in employees

let isDeveloper = e.Title.Contains("Developer")

where (isDeveloper && e.Salary > 35000)

|| (!isDeveloper && e.Salary < 25000)

select e;

 

Projections – eg Select

var names = employees.Where(e => e.Salary > 30000).Select(e => e.Name);

or Anonymous

var namesAndTitles = employees.Select(e => new { e.Name, e.Title });

Or assign to

var namesAndTitles = employees.Select(e => new { Employee = e.Name, Job = e.Title });

.Any()

.SelectMany()  - from http://www.blackwasp.co.uk/LinqOneToManyProjection.aspx  allowing 1 to many to be flattened and queries

Group By Multiple

var query = (from t in Transactions
             group t by new {t.MaterialID, t.ProductID}
             into grp
                    select new
                    {
                        grp.Key.MaterialID,
                        grp.Key.ProductID,
                        Quantity = grp.Sum(t => t.Quantity)
                    }).ToList();
| | # 
# Friday, 11 January 2013
( Linq )

Looking like a good tool in helping with composing linq queries.

| | # 
# Monday, 14 February 2011
( Linq )

http://www.sqltolinq.com/

A tool to convert SQL to Linq.  Uses Linq to SQL or EF.

| | # 
# Wednesday, 08 December 2010
( c# language | Euler | Linq )

This is a fun site:

http://projecteuler.net

The first problem is:

If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.

Find the sum of all the multiples of 3 or 5 below 1000.

static void Main(string[] args)
{
int overallSum = 0;
for (int i = 1; i < 1000; i++)
{
if ((i % 3 == 0) || (i % 5 == 0))
overallSum += i;
}
Console.WriteLine("overall sum is: " + overallSum.ToString());
Console.ReadLine();
}
// should be 233168

or Phils way:

//Find the sum of all the multiples of 3 or 5 below 1000.
//There is a summation formula which states
//The sum of all numbers from i = 1 to i = n is n(n+1)/2
static void Main(string[] args)
{
int total = (3 * Convert.ToInt32(999 / 3) * (Convert.ToInt32((999 / 3)) + 1)) / 2
+ (5 * Convert.ToInt32(999 / 5) * (Convert.ToInt32((999 / 5)) + 1)) / 2
- (15 * Convert.ToInt32(999 / 15) * (Convert.ToInt32((999 / 15)) + 1)) / 2;

Console.WriteLine("overall sum is: " + total.ToString());
Console.ReadLine();

}

from http://answers.yahoo.com/question/index?qid=20100422031504AAfmpJ5)

or Simons way using method syntax (or fluent)

int result = Enumerable.Range(0, 1000)  // generates a sequence of numbers in a range
.Where(i => i % 3 == 0 || i % 5 == 0) // filter using lambda expression
.Sum(); // sum of int32 using extension methods


and for fun, using method and query syntax
 
// get the ones divisible by 3 in the list - Method syntax
IEnumerable<int> listOfDivisibleBy3Method = listOfInts
.Where(i => i % 3 == 0);

// get the ones divisible by 3 in the list - Query syntax
IEnumerable<int> listOfDivisibleBy3Query = from number in listOfInts
where (number % 3 == 0)
select number;

http://stackoverflow.com/questions/214500/which-linq-syntax-do-you-prefer-fluent-or-query-expression

| | # 
# Saturday, 06 November 2010
( Linq | Project )

A windows forms project, with a testing project.  Uses VS2010 and .NET4.  Probably ok with 3.5.

The code is really this:

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
UniqueSpike uniqueSpike = new UniqueSpike();
string messageToDisplay = uniqueSpike.DoChecking();
textBox1.Text = messageToDisplay;
}
}

public class UniqueSpike
{
public string DoChecking()
{
List<thing> listOfThings = SetupTestDataAndAddToList();
List<thing> result = SeeIfAnyInListHaveAUniqueColour(listOfThings);
List<thing> result1 = SeeIfAnyInListAreUniqueByPosition(listOfThings);
List<thing> result2 = SeeIfAnyInListAreUniqueByHeight(listOfThings);
string results = "Single Column Uniqueness" + Environment.NewLine;
foreach (thing thing in result)
results += thing.Name + " becuase its colour is " + thing.Colour + Environment.NewLine;
foreach (thing thing in result1)
results += thing.Name + " becuase its position is " + thing.Colour + Environment.NewLine;
foreach (thing thing in result2)
results += thing.Name + " becuase its height is " + thing.Colour + Environment.NewLine;

results += Environment.NewLine + "Double Column Uniqueness - Colour and Position" + Environment.NewLine;
List<thing> result3 = SeeIfAnyInListAreUniqueByColourAndPosition(listOfThings);
foreach (thing thing in result3)
results += thing.Name + " becuase its colour is " + thing.Colour + " and its position is " + thing.Position + Environment.NewLine;

return results;
}

public List<thing> SetupTestDataAndAddToList()
{
thing thing0 = new thing { Name = "0", Colour = "red", Position = "left", Height = "short" };
thing thing1 = new thing { Name = "1", Colour = "red", Position = "left", Height = "tall" };
thing thing2 = new thing { Name = "2", Colour = "blue", Position = "middle", Height = "short" }; // unique colour and position
thing thing3 = new thing { Name = "3", Colour = "blue", Position = "left", Height = "short" }; // unique colour and position
thing thing4 = new thing { Name = "4", Colour = "green", Position = "right", Height = "medium" }; // unique position right
thing thing5 = new thing { Name = "5", Colour = "green", Position = "middle", Height = "tall" }; // unique colour and position
thing thing6 = new thing { Name = "6", Colour = "orange", Position = "left", Height = "medium" }; // unqiue colour orange
thing thing7 = new thing { Name = "7", Colour = "gold", Position = "left", Height = "medium" }; // unqiue colour gold
List<thing> listOfThings = new List<thing>();
listOfThings.Add(thing0);
listOfThings.Add(thing1);
listOfThings.Add(thing2);
listOfThings.Add(thing3);
listOfThings.Add(thing4);
listOfThings.Add(thing5);
listOfThings.Add(thing6);
listOfThings.Add(thing7);
return listOfThings;
}

public List<thing> SeeIfAnyInListHaveAUniqueColour(List<thing> listOfThings)
{
IEnumerable<IGrouping<string, thing>> thingQuery2 = from t in listOfThings
group t by t.Colour;
List<thing> listOfThingsFound = new List<thing>();
foreach (var thingGroup in thingQuery2)
{
Console.WriteLine(thingGroup.Key);
if (thingGroup.Count() == 1)
{
foreach (thing thing in thingGroup) // there is only going to be 1
listOfThingsFound.Add(thing);
}
}
return listOfThingsFound;
}

public List<thing> SeeIfAnyInListAreUniqueByPosition(List<thing> listOfThings)
{
IEnumerable<IGrouping<string, thing>> thingQuery2 = from t in listOfThings
group t by t.Position;
List<thing> listOfThingsFound = new List<thing>();
foreach (var thingGroup in thingQuery2)
{
Console.WriteLine(thingGroup.Key);
if (thingGroup.Count() == 1)
{
foreach (thing thing in thingGroup) // there is only going to be 1
listOfThingsFound.Add(thing);
}
}
return listOfThingsFound;
}

public List<thing> SeeIfAnyInListAreUniqueByHeight(List<thing> listOfThings)
{
IEnumerable<IGrouping<string, thing>> thingQuery2 = from t in listOfThings
group t by t.Height;
List<thing> listOfThingsFound = new List<thing>();
foreach (var thingGroup in thingQuery2)
{
Console.WriteLine(thingGroup.Key);
if (thingGroup.Count() == 1)
{
foreach (thing thing in thingGroup) // there is only going to be 1
listOfThingsFound.Add(thing);
}
}
return listOfThingsFound;
}

public List<thing> SeeIfAnyInListAreUniqueByColourAndPosition(List<thing> listOfThings)
{
List<thing> listOfThingsFound = new List<thing>();
// grouping by multiple columns as anonymous type
var thingQuery5 = from t in listOfThings.AsEnumerable()
group t by new { colour = t.Colour, position = t.Position } into groupedTable
select new
{
x = groupedTable.Key,
y = groupedTable.Count()
};

foreach (var thing in thingQuery5)
{
if (thing.y == 1) // if there is only 1 record for this colour and position
{
// want to search for the record and add it to a list to return
thing uniqueThingByColourAndPosition = (from t in listOfThings
where (t.Colour == thing.x.colour) && (t.Position == thing.x.position)
select t).Single();
listOfThingsFound.Add(uniqueThingByColourAndPosition);
}
}
return listOfThingsFound;
}
}

public class thing
{
public string Name { get; set; }
public string Colour { get; set; }
public string Position { get; set; }
public string Height { get; set; }
}

and testing code:

[TestClass]
public class UnitTest1
{
[TestMethod]
public void CheckThatNumber6IsUniqueBecauseItIsColourOrange()
{
UniqueSpike uniqueSpike = new UniqueSpike();
List<thing> listOfThings = uniqueSpike.SetupTestDataAndAddToList();
List<thing> result = uniqueSpike.SeeIfAnyInListHaveAUniqueColour(listOfThings);
foreach (thing thing in result)
{
if (thing.Name == "6")
{
thing whatExpecting = new thing { Name = "6", Colour = "orange", Position = "left", Height = "medium" };
Assert.AreEqual(whatExpecting.Colour, thing.Colour);
Assert.AreEqual(whatExpecting.Position, thing.Position);
Assert.AreEqual(whatExpecting.Height, thing.Height);
}
}
}

[TestMethod]
public void CheckThatNumber7IsUniqueBecauseItIsColourGold()
{
UniqueSpike uniqueSpike = new UniqueSpike();
List<thing> listOfThings = uniqueSpike.SetupTestDataAndAddToList();
List<thing> result = uniqueSpike.SeeIfAnyInListHaveAUniqueColour(listOfThings);
foreach (thing thing in result)
{
if (thing.Name == "7")
{
thing whatExpecting = new thing { Name = "7", Colour = "gold", Position = "left", Height = "medium" };
Assert.AreEqual(whatExpecting.Colour, thing.Colour);
Assert.AreEqual(whatExpecting.Position, thing.Position);
Assert.AreEqual(whatExpecting.Height, thing.Height);
}
}
}

[TestMethod]
public void CheckThatNumber4IsUniqueBecauseItIsPositionRight()
{
UniqueSpike uniqueSpike = new UniqueSpike();
List<thing> listOfThings = uniqueSpike.SetupTestDataAndAddToList();

List<thing> result = uniqueSpike.SeeIfAnyInListAreUniqueByPosition(listOfThings);
foreach (thing thing in result)
{
if (thing.Name == "4")
{
thing whatExpecting = new thing { Name = "4", Colour = "green", Position = "right", Height = "medium" };
Assert.AreEqual(whatExpecting.Colour, thing.Colour);
Assert.AreEqual(whatExpecting.Position, thing.Position);
Assert.AreEqual(whatExpecting.Height, thing.Height);
}
}
}

[TestMethod]
public void CheckThatNoneAreReturnedForUniqueHeight()
{
UniqueSpike uniqueSpike = new UniqueSpike();
List<thing> listOfThings = uniqueSpike.SetupTestDataAndAddToList();
List<thing> result = uniqueSpike.SeeIfAnyInListAreUniqueByHeight(listOfThings);
foreach (thing thing in result)
{
Assert.IsFalse(1 == 1); // just throwing an error if it gets here
}
}

[TestMethod]
public void CheckThat_2_3_4_5_6_7_RecordsAreReturnedForColourAndPositionUniqueness() // only doing number 2 so far here
{
UniqueSpike uniqueSpike = new UniqueSpike();
List<thing> listOfThings = uniqueSpike.SetupTestDataAndAddToList();
List<thing> result = uniqueSpike.SeeIfAnyInListAreUniqueByColourAndPosition(listOfThings);
thing thing2 = result.Find(delegate(thing thing)
{
if (thing.Name == "2")
{
return true;
}
return false;
});
Assert.IsNotNull(thing2);

}
}

| | # 
( Linq )

Group by multiple values – however can’t get at the original non grouped data..

http://www.devcurry.com/2009/02/groupby-multiple-values-in-linq.html

| | #