将带有连接的SQL Select语句转换为LINQtoSQL查询 [英] Converting a SQL Select Statement with Joins to a LINQtoSQL query

问题描述

我一直在尝试将SQL语句（下面）转换为LINQtoSQL语句。我开发了一种可怕的解决方法，但效率低得令人难以置信。有没有人知道怎么做而没有一切可怕？



SQL Select声明：

I've been trying to transform a SQL Statement (below) to a LINQtoSQL statement. I developed a horrible workaround, but it's incredible inefficient. Does anyone know how to do it without all horribleness?

SQL Select Statement:

SELECT DISTINCT locationGroups.*
FROM locationGroups
JOIN locations
	ON locationGroups.locationGroupID = locations.locationGroup
JOIN locationRegions
	ON locations.region = locationRegions.regionID
WHERE locationRegions.regionID = 34

我可怕的c＃代码：

My horrible c# code:

public List<locationGroup> GetLocationGroups(locationRegion locationRegion)
        {
            #region Inefficent code

            using (var context = new Entities())
            {
                location[] lA = context.locations.Where(location => location.region == locationRegion.regionID).ToArray();

                List<locationGroup> lG = new List<locationGroup>();

                foreach (location l in lA)
                {
                    if (lG.Any(list => list.locationGroupID == l.locationGroup) == false)
                        lG.Add(context.locationGroups.FirstOrDefault(locationGroup => locationGroup.locationGroupID == l.locationGroup));
                }

                return lG;
            }

            #endregion
        }

推荐答案

我发现这个网站在使用linq时非常有用 101 Linq样本 [ ^ ]



那里有一些连接示例

I find this site most useful when faffing around with linq 101 Linq samples[^]

There are some join examples in there

我去了Mycroft Holmes建议的网站，过了一段时间我终于建了一些有用的东西。我在下面发布了解决方案，以供其他在这个问题上需要帮助的人参考。

I went to Mycroft Holmes suggested site and after awhile I finally built something that worked. I posted the solution below for reference to anyone else who needs help on this issue.

using (var context = new iomniEntities())
            {
                List<locationGroup> lLG = new List<locationGroup>();
                lLG = (from lG in context.locationGroups
                       join l in context.locations on lG.locationGroupID equals l.locationGroup
                       join lR in context.locationRegions on l.region equals lR.regionID
                       where (l.locationTypeID == 9 || l.locationTypeID == 11) && l.active == true && lR.regionID == locationRegion.regionID
                       select lG).Distinct().ToList<locationGroup>();

                return lLG;

            }

这篇关于将带有连接的SQL Select语句转换为LINQtoSQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！