Load a DataGridView from LINQ to SQL query

Question

I'm getting error loading a datagridview from a LINQ query.

I'm making a query to load data from 3 tables, but I always get "Cannot Implicitly convert List to IQueryable. I know a cast exists, but can't figure it out.

So, first, I added a class to my form which will have as properties the values I want to have each row of my join:

public class ListofModuleUser
{
    public string User { get; set; }
    public string Modulo { get; set; }
    public bool Read { get; set; }
    public bool Write { get; set; }
    public bool Create { get; set; }
    public bool Delete { get; set; }
    public bool Navigate { get; set; }
}

Second, I'm making my query and want to return a list to be able to populate my datagridview:

public IQueryable<ListofModuleUser> LoadData()
{
    var ctx = new JEntities();
    var fillusers = (from mu in ctx.tblModuloUsers.AsQueryable()
                     join u in ctx.tblUsers on mu.UserID equals u.UserID
                     join m in ctx.tblModuloes on mu.ModuloID equals m.ModuloID
                     select new
                     {
                         User = u.Username,
                         Modulo = m.Moduloname,
                         Read = mu.CanRead,
                         Write = mu.CanWrite,
                         Create = mu.CanCreate,
                         Delete = mu.CanDelete,
                         Navigate = mu.CanNavigate
                     }).ToList();

    return fillusers; //---> Obviously the error occours here! 
}

And then, on Load event, I'm getting my list and populate my datagridview:

private void MapUserToModulos_List_Load(object sender, EventArgs e)
{
     usersmodules_datagrid.DataSource = LoadData();
}

Can anyone help me figure out which Cast I must use, since I have tried all possible casts (ToList, Array, AsEnumerable, AsQueryable...)

Answer 1

There are 3 important problems in your code.

1. First of all, an IQueryable<T> represents a query that has to be executed and results loaded into memory. You don't want this, the results should be materialized and the client shouldn't know about the query. So, change your return type to IEnumerable<T>:

   public IEnumerable<ListofModuleUser> LoadData()
   

2. Your method claims to return ListofModuleUser but you are projecting to an anonymous object, change that to return the actual class:

   select new ListofModuleUser
   {
       User = u.Username,
       Modulo = m.Moduloname,
       Read = mu.CanRead,
       Write = mu.CanWrite,
       Create = mu.CanCreate,
       Delete = mu.CanDelete,
       Navigate = mu.CanNavigate
   }
   

3. You are not disposing the DbContext instance you are creating, that could easily turn into a memory leak. You should always use the using statement around IDisposable objects:

   using (var ctx = new JEntities())
   {
       ...
   }
   

Putting this all together, your code should look like this:

public IEnumerable<ListofModuleUser> LoadData()
{
    using (var ctx = new JEntities())
    {
        return (from mu in ctx.tblModuloUsers.AsQueryable()
                join u in ctx.tblUsers on mu.UserID equals u.UserID
                join m in ctx.tblModuloes on mu.ModuloID equals m.ModuloID
                select new ListofModuleUser
                {
                    User = u.Username,
                    Modulo = m.Moduloname,
                    Read = mu.CanRead,
                    Write = mu.CanWrite,
                    Create = mu.CanCreate,
                    Delete = mu.CanDelete,
                    Navigate = mu.CanNavigate
                 })
                 .ToList();
    }
}

I would also suggest to rename ListofModuleUser to just ModuleUser since the class represents a single module user.

Answer 2

Your method claims to return an IQueryable<T>:

public IQueryable<ListofModuleUser> LoadData()

But you're trying to return a List<T>:

var fillusers = ....ToList();
return fillusers;

If you want to return a list, change the return type of the method:

public IList<ListofModuleUser> LoadData()

Or, if you want to return an IQueryable, remove the .ToList() from your LINQ query.

---

Additionally, your return type is a specific class:

ListofModuleUser

But you are querying into an anonymous object:

select new
{
    User = u.Username,
    Modulo = m.Moduloname,
    Read = mu.CanRead,
    Write = mu.CanWrite,
    Create = mu.CanCreate,
    Delete = mu.CanDelete,
    Navigate = mu.CanNavigate
}

You have to query to the correct type:

select new ListofModuleUser
{
    User = u.Username,
    Modulo = m.Moduloname,
    Read = mu.CanRead,
    Write = mu.CanWrite,
    Create = mu.CanCreate,
    Delete = mu.CanDelete,
    Navigate = mu.CanNavigate
}

---

Side note: ListofModuleUser is a pretty unintuitive name for a single object. What you are creating is basically called a "List of List of Module User". Perhaps the class should just be called ModuleUser?