Trying to implement an SQL query in LINQ

Question

I'm trying to implement an SQL query in LINQ but I can't find what went wrong.

Original SQL command:

SELECT DISTINCT a.art_id, a.art_title,a.art_permalink, MAX(sp.sp_code), MAX(p.pr_cat_name),  MAX(p.pr_value), MAX(p.pr_cat_id), MAX(p.pr_cat_id)
FROM vArtPart sp
JOIN vArticle a ON (sp.art_id = a.art_id)
JOIN vPrice p ON (a.art_id = p.art_id)
WHERE 
sp.sp_id = 31 AND 
a.art_visible = 1 AND
a.lng_id = 1
GROUP BY a.art_id, a.art_title, a.art_permalink

Linq Query attempt (not working)

var q = (from sp in db.vArtParts
         where sp.sp_id == dd_type.SelectedValue.ToInt()
         join a in db.vArticles on sp.art_id equals a.art_id into aa
         from a in aa.DefaultIfEmpty()
         join p in db.vPrices on a.art_id equals p.art_id into pp
         from p in pp.DefaultIfEmpty()
         where a.art_visible == true && a.lng_id == 1
         group sp by new {a.art_id, a.art_title, a.art_permalink} into g
         select new
             {
                 art_id = (int?)g.art_id,
                 g.art_title,
                 g.art_permalink,
                 sp_code = g.Max(gg=>gg.sp_code),
                 pr_cat_name = g.Max(gg=>gg.pr_cat_name),
                 pr_value = g.Max(gg=>gg.pr_value),
                 pr_cat_id = (int?)g.Max(gg=>gg.pr_cat_id)
             }).ToList();

*upd: the same LINQ query working without grouping (but i need this):

var q = (from sp in db.vArtParts
                 where sp.sp_id == dd_type.SelectedValue.ToInt()
                 join a in db.vArticles on sp.art_id equals a.art_id into aa
                 from a in aa.DefaultIfEmpty()
                 join p in db.vPrices on a.art_id equals p.art_id into pp
                 from p in pp.DefaultIfEmpty()
                 where a.art_visible == true && a.lng_id == 1
                 select new
                     {
                         art_id = (int?)a.art_id,
                         a.art_title,
                         a.art_permalink,
                         sp.sp_code,
                         p.pr_cat_name,
                         p.pr_value,
                         pr_cat_id = (int?)p.pr_cat_id
                     }).ToList();

*upd: Error message:

error CS1061: 'System.Linq.IGrouping' does not contain a definition for 'art_id' and no extension method 'art_id' accepting a first argument of type 'System.Linq.IGrouping' could be found (are you missing a using directive or an assembly reference?)

ASP.global_asax.Application_Error Error System.Web.HttpParseException (0x80004005): ***\ctrl_search_product.ascx.cs(70): error CS1061: 'System.Linq.IGrouping<AnonymousType#1,vArtPart>' does not contain a definition for 'art_id' and no extension method 'art_id' accepting a first argument of type 'System.Linq.IGrouping<AnonymousType#1,vArtPart>' could be found (are you missing a using directive or an assembly reference?) ---> System.Web.HttpCompileException (0x80004005): c:\inetpub\vhosts\domostroy-msk.ru\httpdocs\inc\ctrl_search_product.ascx.cs(70): error CS1061: 'System.Linq.IGrouping<AnonymousType#1,vArtPart>' does not contain a definition for 'art_id' and no extension method 'art_id' accepting a first argument of type 'System.Linq.IGrouping<AnonymousType#1,vArtPart>' could be found (are you missing a using directive or an assembly reference?)
   at System.Web.Compilation.BuildManager.PostProcessFoundBuildResult(BuildResult result, Boolean keyFromVPP, VirtualPath virtualPath)
   at System.Web.Compilation.BuildManager.GetBuildResultFromCacheInternal(String cacheKey, Boolean keyFromVPP, VirtualPath virtualPath, Int64 hashCode, Boolean ensureIsUpToDate)
   at System.Web.Compilation.BuildManager.GetVPathBuildResultFromCacheInternal(VirtualPath virtualPath, Boolean ensureIsUpToDate)
   at System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate)
   at System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate)
   at System.Web.UI.BaseTemplateParser.GetReferencedType(VirtualPath virtualPath, Boolean allowNoCompile)
   at System.Web.UI.BaseTemplateParser.GetUserControlType(VirtualPath virtualPath)
   at System.Web.UI.MainTagNameToTypeMapper.ProcessUserControlRegistration(UserControlRegisterEntry ucRegisterEntry)
   at System.Web.UI.BaseTemplateParser.ProcessDirective(String directiveName, IDictionary directive)
   at System.Web.UI.TemplateControlParser.ProcessDirective(String directiveName, IDictionary directive)
   at System.Web.UI.PageParser.ProcessDirective(String directiveName, IDictionary directive)
   at System.Web.UI.TemplateParser.ParseStringInternal(String text, Encoding fileEncoding)
   at System.Web.UI.TemplateParser.ProcessException(Exception ex)
   at System.Web.UI.TemplateParser.ParseStringInternal(String text, Encoding fileEncoding)
   at System.Web.UI.TemplateParser.ParseString(String text, VirtualPath virtualPath, Encoding fileEncoding)
   at System.Web.UI.TemplateParser.ParseFile(String physicalPath, VirtualPath virtualPath)
   at System.Web.UI.TemplateParser.ParseInternal()
   at System.Web.UI.TemplateParser.Parse()
   at System.Web.Compilation.BaseTemplateBuildProvider.get_CodeCompilerType()
   at System.Web.Compilation.BuildProvider.GetCompilerTypeFromBuildProvider(BuildProvider buildProvider)
   at System.Web.Compilation.BuildProvidersCompiler.ProcessBuildProviders()
   at System.Web.Compilation.BuildProvidersCompiler.PerformBuild()
   at System.Web.Compilation.BuildManager.CompileWebFile(VirtualPath virtualPath)
   at System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate)
   at System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate)
   at System.Web.Compilation.BuildManager.GetVirtualPathObjectFactory(VirtualPath virtualPath, HttpContext context, Boolean allowCrossApp, Boolean throwIfNotFound)
   at System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(VirtualPath virtualPath, Type requiredBaseType, HttpContext context, Boolean allowCrossApp)
   at System.Web.UI.PageHandlerFactory.GetHandlerHelper(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath)
   at System.Web.UI.PageHandlerFactory.GetHandler(HttpContext context, String requestType, String virtualPath, String path)
   at System.Web.HttpApplication.MaterializeHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Compile time or run-time error messages or just a different result? If it's available you can try calling ToTraceString on your IQueryable to check the SQL that your LINQ query will produce. — Mark
– Mark, Commented Jan 22, 2014 at 10:17
Error messages is not available. But query working without grouping, like this (i updated question content) — Antonix
– Antonix, Commented Jan 22, 2014 at 10:28
Is the sp.sp_id = 31 unnecessary? Why aren't you including that in your LINQ? Post the error message because really, how else are we going to determine what's wrong? Also if your LINQ provider is NHibernate you should just give up now. It's not very good with LINQ. — ta.speot.is
– ta.speot.is, Commented Jan 22, 2014 at 10:30
sp.sp_id setted in where sp.sp_id == dd_type.SelectedValue.ToInt() I add a error mesage to my question. — Antonix
– Antonix, Commented Jan 22, 2014 at 12:17

Edward · Accepted Answer · 2014-01-22 14:43:16Z

1

Since you are grouping, in the final select you need

select new
{
                 art_id = (int?)g.key.art_id,
                 g.key.art_title,
                 g.key.art_permalink,

                 ... 
}

Also, but not related to the error you are seeing, in the SQL version you posted you are doing inner join, but in the LINQ version you are doing a left outer join; you should remove the DefaultIfEmpty lines.

Edit: As you are having trouble now with the final Select part, here is the answer to the latter error (note that 'group sp by ...' becomes group new {sp , p } by ...' -

 var q = (from sp in db.vArtParts
 where sp.sp_id == dd_type.SelectedValue.ToInt()
 join a in db.vArticles on sp.art_id equals a.art_id
 join p in db.vPrices on a.art_id equals p.art_id
 where a.art_visible == true && a.lng_id == 1
 group new {sp, p} by new {a.art_id, a.art_title, a.art_permalink} into g
 select new
     {
         art_id = (int?)g.Key.art_id,
         g.Key.art_title,
         g.Key.art_permalink,
         sp_code = g.Max(gg=>gg.sp.sp_code),
         pr_cat_name = g.Max(gg=>gg.p.pr_cat_name),
         pr_value = g.Max(gg=>gg.p.pr_value),
         pr_cat_id = (int?)g.Max(gg=>gg.p.pr_cat_id)
     }).ToList();

edited Jan 22, 2014 at 14:43

answered Jan 22, 2014 at 12:51

Edward

8,7042 gold badges42 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Antonix Over a year ago

I add this code and got error

'vArtPart' does not contain a definition for 'pr_cat_name' and no extension method 'pr_cat_name' accepting a first argument of type 'vArtPart' could be found

How to change these lines?

sp_code = g.Max(gg=>gg.sp_code), pr_cat_name = g.Max(gg=>gg.pr_cat_name), pr_value = g.Max(gg=>gg.pr_value), pr_cat_id = (int?)g.Max(gg=>gg.pr_cat_id)

Collectives™ on Stack Overflow

Trying to implement an SQL query in LINQ

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related