Fixing the error: The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to ‘GroupJoin’. in a left join with LINQ.

If you do a left join in LINQ on multiple columns / properties. The types and names of these properties must be the same else you will get the error:

The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to ‘GroupJoin’.

 

The following code generates the error, because the property [Company] is not the exact same as the property [Comp].

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
public class Person1
{
public string Name { get; set; }
public string Company { get; set; }
}
public class Person2
{
public string Name { get; set; }
public string Comp { get; set; }
}
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Should_join_person1_and_person2()
{
var p1List = new List<Person1>
{
new Person1 { Company = "MyCompany", Name = "John Do"}
};
var p2List = new List<Person2>
{
new Person2 { Comp = "MyCompany", Name = "John Do"}
};
var query = from p1 in p1List
join p2 in p2List on new { p1.Company, p1.Name } equals new { p2.Comp, p2.Name } into p2g
from p2g1 in p2g.DefaultIfEmpty(null)
select p1;
}
}
}

To fix this error add property names to the anonymous objects.

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
public class Person1
{
public string Name { get; set; }
public string Company { get; set; }
}
public class Person2
{
public string Name { get; set; }
public string Comp { get; set; }
}
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Should_join_person1_and_person2()
{
var p1List = new List<Person1>
{
new Person1 { Company = "MyCompany", Name = "John Do"}
};
var p2List = new List<Person2>
{
new Person2 { Comp = "MyCompany", Name = "John Do"}
};
var query = from p1 in p1List
join p2 in p2List on new { Company = p1.Company, Name = p1.Name } 
equals new { Company = p2.Comp, Name = p2.Name } into p2g from p2g1 in p2g.DefaultIfEmpty(null) select p1; } } }

How to update a property of an object in LINQ without returning new objects.

If you want to update an property of an object in LINQ without creating new objects, you can use the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
public class Appointment
{
public int Id { get; set; }
public string Location { get; set; }
}
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Should_update_appointments()
{
var appointments1 = new List<Appointment>
{
new Appointment { Id = 1, Location = "" },
new Appointment { Id = 2, Location = "" },
new Appointment { Id = 3, Location = "" }
};
var appointments2 = new List<Appointment>
{
new Appointment { Id = 1, Location = "My location 1" },
new Appointment { Id = 2, Location = "" },
new Appointment { Id = 3, Location = "My location 3" }
};
Func<Appointment, Appointment, Appointment> UpdateLocation 
= ((a, b) => { a.Location = b.Location; return a; });
var updatedAppointmets = (from a1 in appointments1
join a2 in appointments2 on a1.Id equals a2.Id
select UpdateLocation(a1, a2)).ToList();
foreach (Appointment a in updatedAppointmets)
{
Console.WriteLine
(
string.Format("Id [{0}] Location [{1}]", a.Id, a.Location)
);
}
// Output:
// Id [1] Location [My location 1]
// Id [2] Location []
// Id [3] Location [My location 3]
}
}
}

If you want to use a LEFT OUTER JOIN in the linq query, use:

 

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
public class Appointment
{
public int Id { get; set; }
public string Location { get; set; }
}
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Should_update_appointments()
{
var appointments1 = new List<Appointment>
{
new Appointment { Id = 1, Location = "" },
new Appointment { Id = 2, Location = "" },
new Appointment { Id = 3, Location = "" }
};
var appointments2 = new List<Appointment>
{
new Appointment { Id = 1, Location = "My location 1" },
new Appointment { Id = 3, Location = "My location 3" }
};
Func<Appointment, Appointment, Appointment> UpdateLocation
= ((a, b) => { if (b != null) { a.Location = b.Location; } return a; });
var updatedAppointmets =
(
from a1 in appointments1
join a2 in appointments2 on a1.Id equals a2.Id into g
from g1 in g.DefaultIfEmpty(null)
select UpdateLocation(a1, g1)
).ToList();
foreach (Appointment a in updatedAppointmets)
{
Console.WriteLine
(
string.Format("Id [{0}] Location [{1}]", a.Id, a.Location)
);
}
// Output:
// Id [1] Location [My location 1]
// Id [2] Location []
// Id [3] Location [My location 3]
}
}
}

How to fix: Count on a group by in LINQ returns 1, when used in a left outer join.

If you do a count on a group by in LINQ when using a left outer join, the count will be 1 even though there are no records found for the group. This is caused by the DefaultIfEmpty. The DefaultIfEmpty will insert 1 default object for each group that does not contain any records.

To fix this, adjust the Count, so it ignores the inserted default objects:

 

using System;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
namespace Research.Rli.Tests
{
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Count_should_be_zero_when_left_outer_join_contains_no_values()
{
var statussen = new List<StatusDto> 
{ 
new StatusDto { Id = 0 }, 
new StatusDto { Id = 1 }, 
new StatusDto { Id = 2 } 
};
var actualAlerts = new List<RecordDto> 
{ 
new RecordDto { Id = 1, StatusId = 2 }, 
new RecordDto { Id = 2, StatusId = 2 }, 
new RecordDto { Id = 3, StatusId = 2 }, 
new RecordDto { Id = 4, StatusId = 2 } 
};
int defaultRecordId = -2;
List<ResultDto> totals = (from s in statussen
join aa in actualAlerts on s.Id equals aa.StatusId into gaa
from aaEmpty in gaa.DefaultIfEmpty(new RecordDto { Id = defaultRecordId })
group aaEmpty by s.Id into grp
select new ResultDto 
{ 
StatusId = grp.Key, 
Count = grp.Count(x => x.Id != defaultRecordId) 
}).ToList();
Assert.AreEqual(0, totals[0].Count);
Assert.AreEqual(0, totals[1].Count);
Assert.AreEqual(4, totals[2].Count);
}
}
public class RecordDto
{
public int Id { get; set; }
public int StatusId { get; set; }
}
public class StatusDto
{
public int Id { get; set; }
}
public class ResultDto
{
public int StatusId { get; set; }
public int Count { get; set; }
}
}

How to find and get a specific element from a generic list or collection with LINQ and C#

If you want to find or get an element from a generic list or collection with LINQ and C#, you can use the "First" function:

public ApplicationPool GetApplicationPoolByName(string name)
{
ApplicationPool applicationPool;
using (ServerManager manager = new ServerManager())
{
applicationPool = manager.ApplicationPools.First(a => a.Name == name);
}
return applicationPool;
}

 
 

You can find the Microsoft.Web.Administration.dll in "%windir%\System32\inetsrv".

How to determine if an element exists in a generic list or collection with LINQ and C#

If you want to determine if an element in a generic list or collection exists, use the "Any" function with a lambda expression, like:

        public bool Exists(string name)
{
bool result = false;
using (ServerManager manager = new ServerManager())
{
result = manager.ApplicationPools.Any(a => a.Name == name);
}
return result;
}

You can find the Microsoft.Web.Administration.dll in "%windir%\System32\inetsrv".

Showing a many to many relationship in a GridView by using Entity Framework 4.0, EntityDataSource and data binding in ASP .NET 4.0

The Microsoft SQL Server 2008 R2 sample database AdventureWorks, contains a table Product which as a many to many relationship with the table Document, via the junction table ProductDocument. This post describes the steps you will have to take to show all products and per product all document titles.

 

image

 

As you can see, product “506” has a relationship with multiple documents, so the column “Documents” shows 2 titles.  Because there can be more then one document title to show, I use a BulletedList, but you also could use a  CheckBoxList or DropDownList.

 

The data model

image

 

ASP .NET (*.aspx) page

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" 
CodeBehind="ManyToManyGridView.aspx.cs" 
Inherits="Rvl.Demo.AspNet4.EF.WebApplication.Pages.ManyToManyGridView" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
<link href="../Styles/ManyToManyGridView.css" rel="stylesheet" type="text/css" />
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<p class="pageTitle">Products</p>
<asp:GridView ID="GridView1" runat="server" AllowPaging="true" DataKeyNames="ProductID" 
AutoGenerateColumns="false" PageSize="50" DataSourceID="GridViewEntityDataSource">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="Id" ItemStyle-CssClass="columnPadding" 
HeaderStyle-CssClass="columnPadding" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-CssClass="columnPadding" 
HeaderStyle-CssClass="columnPadding" />
<asp:TemplateField>
<HeaderTemplate>
<asp:Label ID="documentsHeaderLabel" runat="server" Text="Documents"></asp:Label>
</HeaderTemplate>
<HeaderStyle CssClass="columnPadding" />
<ItemStyle CssClass="columnPadding" />
<ItemTemplate>
<asp:BulletedList DataSource='<%# FillBulletedList(Container) %>' 
ID="documentsBulletedList" DataValueField="DocumentID" DataTextField="Title" 
runat="server"></asp:BulletedList>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<!--
- Set EnableFlattening to "false", so you can use real "Product" entities in the FillBulletedList 
function and not wrapper (System.Web.UI.WebControls.EntityDataSourceWrapper) classes, generated 
by the entity framework.
- Don't use the "Include" property, this property should load related entities, like 
"ProductDocument" and "Document", but in my case in did not work, use the "OnQueryCreated" event
to provide a complicated inner join query, that loads related entities
-->
<asp:EntityDataSource ID="GridViewEntityDataSource" runat="server" EnableDelete="false" 
EnableInsert="false" EnableUpdate="false" 
ContextTypeName="Rvl.Demo.AspNet4.EF.WebApplication.Dal.AdventureWorksEntities" 
ConnectionString="name=AdventureWorksEntities" DefaultContainerName="AdventureWorksEntities" 
EntitySetName="Product" EnableFlattening="false" 
OnQueryCreated="GridViewEntityDataSource_QueryCreated">
</asp:EntityDataSource>
</asp:Content>

 

ASP .NET (*.cs) code behind page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Rvl.Demo.AspNet4.EF.WebApplication.Dal;
using System.Data.Objects;
namespace Rvl.Demo.AspNet4.EF.WebApplication.Pages
{
public partial class ManyToManyGridView : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{   
}
public List<Document> FillBulletedList(object container)
{
// Get the product from the current row
GridViewRow gvr = container as GridViewRow;
Product product = gvr.DataItem as Product;
// Create a list of documents related to the current product
List<Document> documents = new List<Document>();
foreach (ProductDocument pd in product.ProductDocument)
{
documents.Add(pd.Document);
}
return documents;
}
protected void GridViewEntityDataSource_QueryCreated(object sender, QueryCreatedEventArgs e)
{
// Load related entities ("ProductDocument" and "Document")
ObjectQuery<Product> query = e.Query.Cast<Product>() as ObjectQuery<Product>;
AdventureWorksEntities context = query.Context as AdventureWorksEntities;
context.Product.Include("ProductDocument").Include("Document");
// Show only product with related documents, by using a inner join (from .... from)
// Show only unique rows by using "Distinct"
// Show products ordered by name, by using "orderby"
var items = from ps in
((from p in query
from pd in p.ProductDocument
select p).Distinct())
orderby ps.Name
select ps;
e.Query = items;
}
}
}

The EntityDataSource use the OnQueryCreated event to provide an inner join query, that loads related entities, like “ProductDocument” and “Document”. It does not use the “Include” property, because that did not work in my case, I don’t no why, but I don’t have time to investigate. The “Include” is done on the Context of the query.

The GridView uses a TemplateField column and the ‘<%# … %>’ syntax to call a function for every row in the GridView to fill a BulletedList that shows all documents per product.

How to sum TimeSpan in LINQ with C#

If you want to sum TimeSpan properties in LINQ with C#, use:

            List<TimeSpan> list = new List<TimeSpan>             {
new TimeSpan(1),
new TimeSpan(2),
new TimeSpan(3)
};
// TimeSpan.Zero is the initial offset, in this case 0 ticks
// subtotal is used to sum to items in the list
// t is the current item in the list
TimeSpan total = list.Aggregate(TimeSpan.Zero, (subtotal, t) => subtotal.Add(t));
Console.WriteLine(total.Ticks);
// Result: 6

 

 

Thanks to: http://stackoverflow.com/questions/970178/c-how-to-use-the-enumerable-aggregate-method

How to get all items from a list that are not unique with LINQ and C#

If you want to get all items from a list that are not unique with LINQ and C#, you can use:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using NUnit.Framework.SyntaxHelpers;
namespace Ada.Cdf.Test
{
[TestFixture]
public class IntegrationTester
{
[Test]
[Explicit("Not a unittest")]
public void Test()
{
List<string> items = new List<string>();
items.Add("Item 1");
items.Add("Item 2");
items.Add("Item 3");
items.Add("Item 4");
items.Add("Item 2");
items.Add("Item 3");
var result = from i in items
group i by i into g
where g.Count() > 1
select new {Group=g.Key, ItemCount= g.Count()};
foreach (var test in result)
{
Console.WriteLine(string.Format("Group[{0}] ItemCount[{1}]", test.Group, test.ItemCount));
}
}
}
}

 

Result

Group[Item 2] ItemCount[2]

Group[Item 3] ItemCount[2]

 

How to count items per category with LINQ and C#

If you want to count items per category with LINQ in C#, you van use:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using NUnit.Framework.SyntaxHelpers;
namespace Ada.Cdf.Test
{
[TestFixture]
public class IntegrationTester
{
[Test]
[Explicit("Not a unittest")]
public void Test()
{
List<string> items = new List<string>();
items.Add("Item 1");
items.Add("Item 2");
items.Add("Item 3");
items.Add("Item 4");
items.Add("Item 2");
items.Add("Item 3");
var result = from i in items
group i by i into g
select new {Group=g.Key, ItemCount= g.Count()};
foreach (var test in result)
{
Console.WriteLine(string.Format("Group[{0}] ItemCount[{1}]", test.Group, test.ItemCount));
}
}
}
}

 

Result

Group[Item 1] ItemCount[1]

Group[Item 2] ItemCount[2]

Group[Item 3] ItemCount[2]

Group[Item 4] ItemCount[1]

 

 

Get index of an item with LINQ and C#

If you want to use the index of an item with LINQ in C#, you can use the “index” statement:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using NUnit.Framework.SyntaxHelpers;
namespace Ada.Cdf.Test
{
[TestFixture]
public class IntegrationTester
{
[Test]
[Explicit("Not a unittest")]
public void Test()
{
List<string> items = new List<string>();
items.Add("Item 1");
items.Add("Item 2");
items.Add("Item 3");
items.Add("Item 4");
items.Add("Item 2");
items.Add("Item 3");
var result = items.Select((item, index) => new { index, item });
//select new { Name=i, Ind=index };
foreach (var test in result)
{
Console.WriteLine(string.Format("item[{0}] index[{1}]", test.item, test.index));
}
}
}
}

Result

item[Item 1] index[0]

item[Item 2] index[1]

item[Item 3] index[2]

item[Item 4] index[3]

item[Item 2] index[4]

item[Item 3] index[5]

1 passed, 0 failed, 0 skipped, took 2,81 seconds (NUnit 2.4).