亚音速3.0和LINQ [英] Subsonic 3.0 and linq
问题描述
我正在使用Subsonic 3.0 SimpleRepository，尝试使用一个linq查询获取菜单和menuitems，但是menuitems始终为空
菜单
public class Menu
{
public Menu()
{
MenuId = 0;
MenuName = "";
MenuItems = null;
}
public int MenuId { get; set; }
public string MenuName { get; set; }
public MenuItem MenuItems { get; set; }
}
菜单项
public class MenuItem
{
public MenuItem()
{
MenuItemId = 0;
MenuId = 0;
MenuItemName = "";
}
public int MenuItemId { get; set; }
public int MenuId { get; set; }
public string MenuItemName { get; set; }
}
Linq查询
var menus = from m in _repo.All<Menu>()
from mi in _repo.All<MenuItem>()
where m.MenuItems.MenuItemId == mi.MenuItemId
select new Menu
{
MenuId = m.MenuId,
MenuName = m.MenuName,
MenuItems = {
MenuItemId = mi.MenuItemId,
MenuItemName = mi.MenuItemName
}
};
有人可以告诉我我在做什么错吗?
我认为我已经找到了解决该问题的实际方法.我一直在SubSonic来源中翻找，发现将数据读取器映射到对象时使用两种类型的对象投影:一种用于匿名类型和分组，另一种用于其他所有东西
以下是代码段:SubSonic.Linq.Structure.DbQueryProvider的269-298行
IEnumerable<T> result;
Type type = typeof (T);
//this is so hacky - the issue is that the Projector below uses Expression.Convert, which is a bottleneck
//it's about 10x slower than our ToEnumerable. Our ToEnumerable, however, stumbles on Anon types and groupings
//since it doesn't know how to instantiate them (I tried - not smart enough). So we do some trickery here.
if (type.Name.Contains("AnonymousType") || type.Name.StartsWith("Grouping`") || type.FullName.StartsWith("System.")) {
var reader = _provider.ExecuteReader(cmd);
result = Project(reader, query.Projector);
} else
{
using (var reader = _provider.ExecuteReader(cmd))
{
//use our reader stuff
//thanks to Pascal LaCroix for the help here...
var resultType = typeof (T);
if (resultType.IsValueType)
{
result = reader.ToEnumerableValueType<T>();
}
else
{
result = reader.ToEnumerable<T>();
}
}
}
return result;
结果表明，SubSonic ToEnumerable尝试将数据读取器中的列名与您要投影到的对象中的属性进行匹配.我的Linq发出的SQL查询看起来像这样:
SELECT [t0].[Id], [t0].[ProductId], [t0].[ReleaseDate], [t0].[ReleasedBy], [t0].[ReleaseNumber], [t0].[RevisionNumber], [t0].[c0]
FROM (
SELECT [t1].[Id], [t1].[ProductId], [t1].[ReleaseDate], [t1].[ReleasedBy], [t1].[ReleaseNumber], [t1].[RevisionNumber], (
SELECT COUNT(*)
FROM [dbo].[Install] AS t2
WHERE ([t2].[ReleaseId] = [t1].[Id])
) AS c0
FROM [dbo].[Release] AS t1
) AS t0
WHERE ([t0].[ProductId] = 2)
请注意[t0].[c0]与我的属性名称NumberOfInstalls不同.因此，c0的值永远不会投影到我的对象中.
解决方法: 您只需取出if语句，然后使用慢10倍的投影，一切都会正常进行.
I'm playing around with Subsonic 3.0 SimpleRepository and try to get menus and menuitems with one linq query, but the menuitems is allways null
Menu
public class Menu
{
public Menu()
{
MenuId = 0;
MenuName = "";
MenuItems = null;
}
public int MenuId { get; set; }
public string MenuName { get; set; }
public MenuItem MenuItems { get; set; }
}
Menuitem
public class MenuItem
{
public MenuItem()
{
MenuItemId = 0;
MenuId = 0;
MenuItemName = "";
}
public int MenuItemId { get; set; }
public int MenuId { get; set; }
public string MenuItemName { get; set; }
}
Linq query
var menus = from m in _repo.All<Menu>()
from mi in _repo.All<MenuItem>()
where m.MenuItems.MenuItemId == mi.MenuItemId
select new Menu
{
MenuId = m.MenuId,
MenuName = m.MenuName,
MenuItems = {
MenuItemId = mi.MenuItemId,
MenuItemName = mi.MenuItemName
}
};
Can some one tell me what am I doing wrong here ?
I think I've found the actual answer to this problem. I've been rummaging around in the SubSonic source and found that there are two types of object projection that are used when mapping the datareader to objects: one for anonymous types and groupings and one for everything else:
Here is a snippet: Line 269 - 298 of SubSonic.Linq.Structure.DbQueryProvider
IEnumerable<T> result;
Type type = typeof (T);
//this is so hacky - the issue is that the Projector below uses Expression.Convert, which is a bottleneck
//it's about 10x slower than our ToEnumerable. Our ToEnumerable, however, stumbles on Anon types and groupings
//since it doesn't know how to instantiate them (I tried - not smart enough). So we do some trickery here.
if (type.Name.Contains("AnonymousType") || type.Name.StartsWith("Grouping`") || type.FullName.StartsWith("System.")) {
var reader = _provider.ExecuteReader(cmd);
result = Project(reader, query.Projector);
} else
{
using (var reader = _provider.ExecuteReader(cmd))
{
//use our reader stuff
//thanks to Pascal LaCroix for the help here...
var resultType = typeof (T);
if (resultType.IsValueType)
{
result = reader.ToEnumerableValueType<T>();
}
else
{
result = reader.ToEnumerable<T>();
}
}
}
return result;
Turns out that the SubSonic ToEnumerable tries to match the column names in the datareader to the properties in the object you're trying to project to. The SQL Query from my Linq looks like this:
SELECT [t0].[Id], [t0].[ProductId], [t0].[ReleaseDate], [t0].[ReleasedBy], [t0].[ReleaseNumber], [t0].[RevisionNumber], [t0].[c0]
FROM (
SELECT [t1].[Id], [t1].[ProductId], [t1].[ReleaseDate], [t1].[ReleasedBy], [t1].[ReleaseNumber], [t1].[RevisionNumber], (
SELECT COUNT(*)
FROM [dbo].[Install] AS t2
WHERE ([t2].[ReleaseId] = [t1].[Id])
) AS c0
FROM [dbo].[Release] AS t1
) AS t0
WHERE ([t0].[ProductId] = 2)
Notice the [t0].[c0] is not the same as my property name NumberOfInstalls. So the value of c0 never gets projected into my object.
THE FIX: You can simply take out the if statement and use the 10x slower projection and everything will work.
这篇关于亚音速3.0和LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
