>>use a linq join
>
>I am having problem trying to implement a join solution. Here is my last attempt (this one doesn't compile):
>
>

>public override SChannels GetById(object id)
>        {
>            //var sChannel = _siriusContext.SChannels.Include("ScsgLink")         
>
>            // .Where(x => x.ChannelId == (Int32)id)
>            // .First();
>
>            //foreach(ScsgLink link in sChannel.ScsgLink)
>            //{
>            //    link.GroupDescription = _siriusContext.SpGroup.Where(sp => sp.GroupNo == link.GroupNo).FirstOrDefault().Descrip.Trim();
>            //}
>
>            var sChannel = base.GetById(id);
>
>            var query = from links in _siriusContext.ScsgLink
>  join groups in _siriusContext.SpGroup
>      on links.GroupNo equals groups.GroupNo
>                        where links.ChannelId == (Int32)id
>  select new { ChannelId = links.ChannelId, GroupNo = links.GroupNo, ExpireHrs = links.ExpireHrs, Percentage = links.Percentage, GroupDescription = groups.Descrip.Trim()   };
>
>            sChannel.ScsgLink = (ScsgLink) (query).ToList();
>            return sChannel;
>        }

>
>The commented code works, but it's not efficient. The uncommented code doesn't work when I try to put ScsgLink in front of the new operator giving me an error in run-time:
>
>System.NotSupportedException was unhandled by user code
> HResult=-2146233067
> Message=The entity or complex type 'Siriusware.Data.ScsgLink' cannot be constructed in a LINQ to Entities query.
>
>---------------------
>UPDATE. Loop works, I haven't yet checked the query behind, though:
>
>

> var query = from links in _siriusContext.ScsgLink
>  join groups in _siriusContext.SpGroup
>      on links.GroupNo equals groups.GroupNo
>                        where links.ChannelId == (Int32)id
>  select new {
>      ScsgLinkId = links.ScsgLinkId,
>
>      ChannelId = links.ChannelId,
>                
>      GroupNo = links.GroupNo, ExpireHrs = links.ExpireHrs, Percentage = links.Percentage, GroupDescription = groups.Descrip.Trim()   };
>
>            sChannel.ScsgLink = new List<ScsgLink>();
>            foreach(var link in query)
>            {
>                sChannel.ScsgLink.Add(new ScsgLink {
>                    ScsgLinkId = link.ScsgLinkId,
>                    ChannelId = link.ChannelId, GroupNo = link.GroupNo,
>                    ExpireHrs = link.ExpireHrs,
>                    Percentage = link.Percentage,
>                    GroupDescription = link.GroupDescription
>                } );            }
>            return sChannel;

>
>Also, I didn't re-try the using ScgsLink in front of the new yet, but in my original try I forgot to add PK in the list of columns. In this code I use PK in both places.

Is there a navigation property from your ScgsLink to your SpGroup? If not, there should be. Then you won't need a GroupDescription property in ScgsLink, since you can just use scsgLink.SpGroup.Descrip. Your query also remains simple as:

public override SChannels GetById(object id)
        {
            var sChannel = _siriusContext.SChannels.Include("ScsgLink")         
             .Include("ScsgLink.SpGroup")  
             .Where(x => x.ChannelId == (Int32)id)
             .First();

            return sChannel;
}