In one database we have 2 tables that are joined with a foreign key. In a separate database we have a table that needs to be filled with the result of an inner join from the first database. Here is the first attempt at doing this:
public void Join_TwoTablesWithLinqQuery_IsMappedToTargetEntity()
{
using (this.databaseContext.BeginTransaction())
{
int modelRunId = 18406;
//Earthquake handles vulnerability functions as strings where as Tropical Cyclone handles them as integers. Therefore we need to
//have some special code to map these together
var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
on av.VulnerabilityFunctionId equals vf.Id
select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
{ object initializer
Id = av.Id,
VfId = int.Parse(vf.VulnerabilityFunctionString), // not safe yet
BuildingQuality = av.BuildingQuality,
BuildingType = av.BuildingType,
CoveredRisk = av.CoveredRisk,
MainOccupancyType = av.MainOccupancyType,
OccupancyModifier = av.OccupancyModifier
};
Assert.IsTrue(result.Count() > 0, "this ModelRun should have VAs");
}
}
We want to make the conversion of the VulnerabilityFunctionString to integer safer. So we refactored this into a Func
Func<string, int> convertToInt = (vfString) =>
{
int value = 0;
int.TryParse(vfString, out value);
return value;
};
var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
on av.VulnerabilityFunctionId equals vf.Id
select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
{
Id = av.Id,
VfId = convertToInt(vf),
BuildingQuality = av.BuildingQuality,
BuildingType = av.BuildingType,
CoveredRisk = av.CoveredRisk,
MainOccupancyType = av.MainOccupancyType,
OccupancyModifier = av.OccupancyModifier
};
An alternative would be to make this in line:
var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
on av.VulnerabilityFunctionId equals vf.Id
select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
{
Id = av.Id,
VfId = (vfString) =>
{
int value = 0;
int.TryParse(vfString, out value);
return value;
},
BuildingQuality = av.BuildingQuality,
BuildingType = av.BuildingType,
CoveredRisk = av.CoveredRisk,
MainOccupancyType = av.MainOccupancyType,
OccupancyModifier = av.OccupancyModifier
};
Next we refactored the Func into a static class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Ccp.Infrastructure
{
/// <summary>
/// Helper functions and actions to convert data types.
/// </summary>
public static class Conversions
{
/// <summary>
/// Safely convert int to string, with default to 0.
/// </summary>
public static Func<string, int> ConvertToInt = (vfString) =>
{
int value = 0;
int.TryParse(vfString, out value);
return value;
};
}
}
[TestMethod]
public void Join_TwoTablesWithLinqQuery_IsMappedToTargetEntity()
{
using (this.databaseContext.BeginTransaction())
{
// need a model run identifier first, that has corresponding vulnerability aggregates
int modelRunId = 18406;
//Earthquake handles vulnerability functions as strings where as Tropical Cyclone handles them as integers. Therefore we need to
//have some special code to map these together
var result = from av in this.databaseContext.GetAll<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
join vf in this.databaseContext.GetAll<VulnerabilityFunction>()
on av.VulnerabilityFunctionId equals vf.Id
select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
{
Id = av.Id,
VfId = Conversions.ConvertToInt(vf.VulnerabilityFunctionString),
BuildingQuality = av.BuildingQuality,
BuildingType = av.BuildingType,
CoveredRisk = av.CoveredRisk,
MainOccupancyType = av.MainOccupancyType,
OccupancyModifier = av.OccupancyModifier
};
Assert.IsTrue(result.Count() > 0, "this ModelRun should have VAs");
}
}
}
This worked well but the GetAll function first loads everything to the client and then the client makes the join. We want the join to be made on the sql server so we changed GetAll to Query as shown below:
var result = from av in this.databaseContext.Query<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
join vf in this.databaseContext.Query<VulnerabilityFunction>()
on av.VulnerabilityFunctionId equals vf.Id
select new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
{
Id = av.Id,
VfId = Conversions.ConvertToInt(vf.VulnerabilityFunctionString),
BuildingQuality = av.BuildingQuality,
BuildingType = av.BuildingType,
CoveredRisk = av.CoveredRisk,
MainOccupancyType = av.MainOccupancyType,
OccupancyModifier = av.OccupancyModifier
};
But we got the following exception:
Test method Ccp.DataAccessLayerTest.DatabaseContextTests.Join_TwoTablesWithLinqQuery_IsMappedToTargetEntity threw exception:
System.NotSupportedException: The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.
So we tried to split the function into first the query and then the convert.
var result = from av in this.databaseContext.Query<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
join vf in this.databaseContext.Query<VulnerabilityFunction>()
on av.VulnerabilityFunctionId equals vf.Id
select new
{
av.Id,
vf.VulnerabilityFunctionString,
av.BuildingQuality,
av.BuildingType,
av.CoveredRisk,
av.MainOccupancyType,
av.OccupancyModifier
};
IList<Ccp.Entities.TropicalCyclone.VulnerabilityAggregate> vaList = result.Select((av) => new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
{
Id = av.Id,
VfId = Conversions.ConvertToInt(av.VulnerabilityFunctionString),
BuildingQuality = av.BuildingQuality,
BuildingType = av.BuildingType,
CoveredRisk = av.CoveredRisk,
MainOccupancyType = av.MainOccupancyType,
OccupancyModifier = av.OccupancyModifier
}).ToList();
The problem with the above code is that var result is only loaded when the select is called. This means that we are sill trying to execute a Func within an SQL query. The underlying problem is that we have built up a query tree in the business domain using Linq which includes a function (Func) that there is no corresponding mapping to in the Storage Domain
So the solution was:
[TestMethod]
public void Join_TwoTablesWithLinqQuery_IsMappedToTargetEntity()
{
using (this.databaseContext.BeginTransaction())
{
// need a model run identifier first, that has corresponding vulnerability aggregates
int modelRunId = 18406;
//Earthquake handles vulnerability functions as strings where as Tropical Cyclone handles them as integers. Therefore we need to
//have some special code to map these together
var result = from av in this.databaseContext.Query<VulnerabilityAggregate>().Where(z => z.ModelRunId.Value.Equals(modelRunId))
join vf in this.databaseContext.Query<VulnerabilityFunction>()
on av.VulnerabilityFunctionId equals vf.Id
select new
{
av.Id,
vf.VulnerabilityFunctionString,
av.BuildingQuality,
av.BuildingType,
av.CoveredRisk,
av.MainOccupancyType,
av.OccupancyModifier
};
var vaList = result.ToList().Select((av) => new Ccp.Entities.TropicalCyclone.VulnerabilityAggregate
{
Id = av.Id,
VfId = Conversions.ConvertToInt(av.VulnerabilityFunctionString),
BuildingQuality = av.BuildingQuality,
BuildingType = av.BuildingType,
CoveredRisk = av.CoveredRisk,
MainOccupancyType = av.MainOccupancyType,
OccupancyModifier = av.OccupancyModifier
});
Assert.IsTrue(vaList.Count() > 0, "this ModelRun should have VAs");
}
This emitted the following SQL code:
exec sp_executesql N'SELECT
[Extent1].[VULNERABILITY_AGGREGATE_ID] AS [VULNERABILITY_AGGREGATE_ID],
[Extent2].[VULNERABILITY_FUNCTION] AS [VULNERABILITY_FUNCTION],
[Extent1].[BUILDING_QUALITY] AS [BUILDING_QUALITY],
[Extent1].[BUILDING_TYPE] AS [BUILDING_TYPE],
[Extent1].[COVERED_RISK] AS [COVERED_RISK],
[Extent1].[MAIN_OCCUPANCY_TYPE] AS [MAIN_OCCUPANCY_TYPE],
[Extent1].[OCCUPANCY_MODIFIER] AS [OCCUPANCY_MODIFIER]
FROM [dbo].[MOD_VULNERABILITY_AGGREGATE] AS [Extent1]
INNER JOIN [dbo].[MOD_VULNERABILITY_FUNCTION] AS [Extent2] ON [Extent1].[VULNERABILITY_FUNCTION_ID] = [Extent2].[VULNERABILITY_FUNCTION_ID]
WHERE [Extent1].[MODEL_RUN_ID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=18406
Some Lessons Learned
When we try to make complex select statements in Linq we struggle with the Linq syntax. Since knowledge of Linq at the complex end of the query spectrum is sparse we recommend that complex select statements be made as views. The reason is that the knowledge of building complex SQL statements is wide spread where are the same operation in Linq takes a long time to figure out and the knowledge out there is sparse.
Another thing is Update statements such as "Update table set field= 111 where filed2=2" cannot be easily made within EF. We recommended when this is necesary for performance reasons that stored procedures be used
Recomended book http://www.amazon.de/Programming-Microsoft%C2%AE-PRO-Developer-Paolo-Pialorsi/dp/0735624003/ref=sr_1_3?ie=UTF8&s=books-intl-de&qid=1274251357&sr=1-3
Programming Microsoft® LINQ (PRO-Developer) (Taschenbuch)
von Paolo Pialorsi (Autor), Marco Russo (Autor)
Looking at some more complex SQL statements such as Group by
First a quick tutorial from http://www.sql-tutorial.net/SQL-HAVING.asp
The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.
You can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used. For example the following SQL statement will generate an error:
SELECT Employee, SUM (Hours)
FROM EmployeeHours
WHERE SUM (Hours) > 24
GROUP BY Employee
The SQL HAVING clause is used to do exactly this, to specify a condition for an aggregate function which is used in your query:
SELECT Employee, SUM (Hours)
FROM EmployeeHours
GROUP BY Employee
HAVING SUM (Hours) > 24
The above SQL statement will select all employees and the sum of their respective hours, as long as this sum is greater than 24. The result of the SQL HAVING clause can be seen below:
Employee Hours
John Smith 25
Tina Crown 27
Here are some examples of how this gets translated into Linq together with the emitted sql:
[TestInitialize]
public void TestInitialize()
{
objectContext = new HotelModelContainer();
target = new DatabaseContext(objectContext);
target.DeleteAll<Customer>();
target.DeleteAll<Reservation>();
target.DeleteAll<Hotel>();
target.DeleteAll<Price>();
target.DeleteAll<Chain>();
target.DeleteAll<Room>();
target.DeleteAll<City>();
target.DeleteAll<Currency>();
Currency chf = new Currency { Symbol = "CHF", Name = "Schweizer Franken" };
City city1 = new City { Code = "LON", Name = "London", Currency = chf };
City city2 = new City { Code = "LON2", Name = "London", Currency = chf };
City city3 = new City { Code = "ZRH", Name = "Zürich", Currency = chf };
Price price = new Price { Currency = chf, Value = 60.0M };
this.target.Add(city1, city2, city3);
target.Save();
}
[TestMethod]
public void GroupBy_HavingCountBiggerThanOne_Returns()
{
using (this.target.BeginTransaction())
{
var result = from c in this.target.Query<City>()
group c by c.Name into cg
where cg.Count() > 1
select cg;
var res = result.ToList();
Assert.AreNotEqual<int>(0, res.Count);
//SELECT
//[Project1].[C1] AS [C1],
//[Project1].[Name] AS [Name],
//[Project1].[C2] AS [C2],
//[Project1].[Id] AS [Id],
//[Project1].[Name1] AS [Name1],
//[Project1].[Code] AS [Code],
//[Project1].[Currency_Id] AS [Currency_Id]
//FROM ( SELECT
// [GroupBy1].[K1] AS [Name],
// 1 AS [C1],
// [Extent2].[Id] AS [Id],
// [Extent2].[Name] AS [Name1],
// [Extent2].[Code] AS [Code],
// [Extent2].[Currency_Id] AS [Currency_Id],
// CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
// FROM (SELECT
// [Extent1].[Name] AS [K1],
// COUNT(1) AS [A1]
// FROM [dbo].[Cities] AS [Extent1]
// GROUP BY [Extent1].[Name] ) AS [GroupBy1]
// LEFT OUTER JOIN [dbo].[Cities] AS [Extent2] ON [GroupBy1].[K1] = [Extent2].[Name]
// WHERE [GroupBy1].[A1] > 1
//) AS [Project1]
//ORDER BY [Project1].[Name] ASC, [Project1].[C2] ASC
}
}
Here is an example with Group by and having
[TestMethod]
public void GroupBy_Having_Returns()
{
using (this.target.BeginTransaction())
{
int count = this.target.Query<City>().GroupBy<City, string>(c => c.Name).Where(g => g.Count() > 1).Count();
Assert.IsTrue(count > 0);
//SELECT
//[GroupBy2].[A1] AS [C1]
//FROM ( SELECT
// COUNT(1) AS [A1]
// FROM ( SELECT
// [Extent1].[Name] AS [K1],
// COUNT(1) AS [A1]
// FROM [dbo].[Cities] AS [Extent1]
// GROUP BY [Extent1].[Name]
// ) AS [GroupBy1]
// WHERE [GroupBy1].[A1] > 1
//) AS [GroupBy2]
}
}
I encountered one last problem. I made a change to the database and wanted this change to be reflected in the data model within the edm. Unfortunately the only way to get this to work is to erase the entity from within the edm and drag int back in. This is quite annoying because all the mappings to that table are lost in the processed. Apparently this is by design http://www.itweb.co.za/visualstudio/index.php?option=com_ninjaboard&view=topic&topic=4531&Itemid=2
8<---
Update model from database does not check for allow nulls?
on 04/16/2010 04:30
After setting up a vs2010 EF model for a simple sql server 2008 db, I changed a column in the db so that it would not allow nulls and saved the db. I then ran 'Update model from database' in vs expecting the model to reflect the change. But the model property for that column remained (None) for Nullable. Surely evaluation of allow nulls is included during a refresh from the db?
Update model from database does not check for allow nulls?
on 04/12/2010 06:40
Hi, It is a designed behavior. “Update model from database” will only refresh the modifications in the SSDL (Storage Metadata Schema of the EDM. If we open the .edmx via XML editor (right click the .edmx à Open With… à Select “XML Editor”). In the SSDL section, we can see that certain property has marked as Nullable=”false”. However, the CSDL (Conceptual Schema) is designed to be maintained by the users. Generally, “Update model from database” won’t refresh the CSDL section unless no mapping properties is defined at the conceptual model, e.g. we add a new column in the database and then use “Update model from database”, EF will add a new property at the CSDL in this case. Also, imaging we have many inheritance, merging and splitting tables in the EDM, if “Update model from database” refreshes the CSDL, our manual modification of the EDM will be lost. In your scenario, we need to set the certain property’s Nullable to False at the EDM designer. If you have any questions, please feel free to let me know. Have a nice day! Best Regards, Lingzhi Sun MSDN Subscriber Support in Forum If you have any feedback on our support, please contact msdnmg@microsoft.com Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
8<---
Dealing with NUMERIC with Automapper
We start by configuring the mapper as follows
Mapper.CreateMap<Ccp.Entities.TESTCS, Ccp.Entities.EuropeanWind.TESTWS>()
.ForMember(d => d.TestFloat, c => c.MapFrom<double?>(s => s.test1))
.ForMember(d => d.TestNumeric, c => c.MapFrom<double?>(s => s.test2));
Then we make a unit test that reproduces the error:
[TestMethod]
public void Mapping_TableWithFloatsToTableWithNumeric_DataTransfered()
{
// arrange stuff
this.objectContextEuropeanWind = new EuropeanWindEntities(ConfigurationManager.ConnectionStrings["EuropeanWindEntities"].ConnectionString);
this.databaseContextEuropeanWind = new DatabaseContext(objectContextEuropeanWind);
IList<Ccp.Entities.TESTCS> input = this.databaseContext.GetAll<Ccp.Entities.TESTCS>().ToList();
Mappings.Initialize();
// act
Ccp.Entities.EuropeanWind.TESTWS destination = Mapper.Map<Ccp.Entities.TESTCS, Ccp.Entities.EuropeanWind.TESTWS>(input.FirstOrDefault());
// assert
Assert.IsNotNull(destination, "mapping did not work");
using (this.databaseContextEuropeanWind.BeginTransaction())
{
this.databaseContextEuropeanWind.Add(destination);
this.databaseContextEuropeanWind.CommitTransaction();
}
}
This through the following error:
Trying to map Ccp.Entities.TESTCS to Ccp.Entities.EuropeanWind.TESTWS.
Using mapping configuration for Ccp.Entities.TESTCS to Ccp.Entities.EuropeanWind.TESTWS
Exception of type 'AutoMapper.AutoMapperMappingException' was thrown.
The solution was to change the mapping as follows:
Mapper.CreateMap<Ccp.Entities.TESTCS, Ccp.Entities.EuropeanWind.TESTWS>()
.ForMember(d => d.TestFloat, c => c.MapFrom<double?>(s => s.test1))
.ForMember(d => d.TestNumeric, c => c.MapFrom<decimal?>(s => (decimal?)s.test2));
