(Group)Join if you can't use the ICollection

Mouhssine tahri

5y824.NET

Hi,

I have 2 table :(User and Service) with relation many to many

the EF create third table ServiceUserDiff

public partial class User
{
public int UserID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public virtual ICollection UserDiffToService { get; set; }
}
public partial class Service
{
public int CodeService { get; set; }
public string DesignationService { get; set; }
public virtual ICollection ServiceToUserDiff { get; set; }
}
public partial class ServiceUserDiff
{
public Nullable<int> IdUserDiffService { get; set; }
public Nullable<int> IdServiceUserDiff { get; set; }
public Nullable<int> AttributaireON { get; set; }
public int IdDiff { get; set; }
public virtual Service ServiceDiff { get; set; }
public virtual User UserDiff { get; set; }
}

i create function to get information from 3 table

public List GetAllServiceUserDiff()
{
var listServiceUserDiff = bd.Service.GroupJoin(bd.ServiceUserDiff,
service => service.CodeService,
serviceUserDiff => serviceUserDiff.IdServiceUserDiff,
(service, serviceUserDiff) => new
{
serviceCode = service.CodeService,
serviceDsg = service.DesignationService,
user = serviceUserDiff.Join(bd.User,
serviceUserDiffs => serviceUserDiffs.IdUserDiffService,
user => user.UserID,
(user, serviceUserDiffs) => new
{
IdUser = serviceUserDiffs.UserID,
NameLast = serviceUserDiffs.LastName,
NameFirst = serviceUserDiffs.FirstName
}).ToList(),
});
return listServiceUserDiff;
}

this function generates me the next error message:

Cannot implicitly convert type system linq IQueryable to system collections generic List

thanks for your help

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R5y ago
Accepted answer
Hi Tahri,
Try This code:
var contacts = (from a in db.TableA
                where a.PrimaryID == id
                join b in db.Contacts on a.PrimaryID equals b.PrimaryID
                join c in db.tableC on b.PrimaryID equals c.ParentPrimaryID
                join d in db.Contacts on c.ChildID equals d.PrimaryID
                select new { d.PrimaryID, d.FirstName, d.LastName } ).Distinct();

var query = (from d in contacts
             join e in db.TablePhoneNbrs on d.PrimaryID equals e.PrimaryID into ej
             select new {
                 PrimaryId = d.PrimaryID,
                 FirstName = d.FirstName,
                 LastName = d.LastName,
                 phhoneNbrs = ej
             });
Mouhssine tahri5y ago
1. public ServiceUserDiff GetAllServiceUserDiff(int idUser)
2. {
3. var services = (from a in bd.ServiceUserDiff
4. where a.IdServiceUserDiff == idUser
5. join c in bd.User on a.IdUserDiffService equals c.UserID
6. select new { c.FirstName, c.LastName }).Distinct();
7. return services ;
8. }
in bridge table ServiceUserDiff i have idUser and i want these firstname and lastname from user Tablex

but i have error message in return services

Cannot implicitly convert type system linq IQueryable <> to DATA.ServiceUserDiff

PLEASE HELP ME

Mouhssine tahri5y ago

thanks Mageshwaran R

in my example not like your query

i have 2 table and bridge table (relation many to many)

Mouhssine tahri5y ago

thanks

Ronika Jency

i have the same error

Mouhssine tahri5y ago

perhaps my question is not clear

in my bridge table ServiceUserDiff i have the next record :

IdUserDiffService IdServiceUserDiff AttributaireON IdDiff

1 11 1 1

select DesignationService ,LastName,FirstName

from User,Service,ServiceUserDiff

where User.UserID=ServiceUserDiff.IdUserDiffService

and Service.CodeService = ServiceUserDiff.IdServiceUserDiff

and ServiceUserDiff.IdServiceUserDiff=11

and ServiceUserDiff.IdUserDiffService=1

can you help me transform this query to Linq lambda and implement it in apicontroller

Ronika Jency5y ago

Hi,

var listServiceUserDiff = bd.Service.GroupJoin(bd.ServiceUserDiff,

service => service.CodeService,

serviceUserDiff => serviceUserDiff.IdServiceUserDiff,

(service, serviceUserDiff) => new

{

serviceCode = service.CodeService,

serviceDsg = service.DesignationService,

user = serviceUserDiff.Join(bd.User,

serviceUserDiffs => serviceUserDiffs.IdUserDiffService,

user => user.UserID,

(user, serviceUserDiffs) => new

{

IdUser = serviceUserDiffs.UserID,

NameLast = serviceUserDiffs.LastName,

NameFirst = serviceUserDiffs.FirstName

}).ToList(),

}).ToList();

last line, you have to put tolist () to get the data as list of values