MVC model coding with table model and controller

Question

Hi I have some coding issue here, what I am trying to do is based on the input SYSID, find the list of name from table, just for testing purpose.

Model:

public DbSet<firsttest> firsttests { get; set; }

Table:

  [Table("FirstTest")]
    public class firsttest
    { 
        public firsttest(){}

        [Column("sysId")]
        public long SysId { get; set; }
        public string Name { get; set; }
    }

Controller:

    [Authorize]
    [Route("api/FirstTest/{SysId}")]
    public List<String> Name(long SYSID)
    {
        using (AContext db = new AContext())
        {
            var query = db.firsttests.Where(a => 1 == 1);
            if (!String.IsNullOrEmpty(SYSID.ToString()))
            {
                query = query.Where(a => a.SysId.Equals(SYSID)).Select(a => a.Name);
            }
            return query.ToList();
        }
    }

The error is : cannot implicitly convert type System.Linq.Iqueryable(string) to System.Linq.Iqueryable(Model.firsttest)

Any idea how to modify the code?

score 1 · Answer 1 · answered Feb 07 '17 at 14:12

try this.

[Authorize]
[Route("api/FirstTest/{SysId}")]
public List<String> Name(long SYSID)
{
    using (SpaContext db = new SpaContext())
    {
        var query = db.firsttests.Where(a => 1 == 1);
        if (!String.IsNullOrEmpty(SYSID.ToString()))
        {
            query = query.Where(a => a.SysId.Equals(SYSID));
        }
        return query.Select(a => a.Name).ToList();
    }
}

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

You can just filter the results there in the if block and then project just before returning the List<string> like:

if (!String.IsNullOrEmpty(SYSID.ToString()))
{
    query = query.Where(a => a.SysId.Equals(SYSID)); // only filter here
}
return query.Select(a => a.Name).ToList(); // now project the result finally

This behaviour you are seeing is because of the nature of the way var works in C#.

When you write var query = db.firsttests.Where(a => 1 == 1); , the variable query is now of type IQueryable<Model.firsttest> and now down in the code you cannot assign to variable query a reference of different type which in your case after project the result will become System.Linq.Iqueryable<string>.

You can read about it in more detail the following :

https://msdn.microsoft.com/en-us/library/bb384061.aspx

What does "var" mean in C#?

edited May 23 '17 at 12:24

Community

1
1

answered Feb 07 '17 at 14:16

Ehsan Sajjad

61,834
16
105
160

this works for me! But why I cannot specify the `select` before `return` like my code? Do I need explicit conversion method? – LONG Feb 07 '17 at 14:21
it's because when you write `var query = db.firsttests.Where(a => 1 == 1);`, the `query` is now strongly typed with `IQueryable` not down in the code you cannot assign query reference of different type which in your query will become `System.Linq.Iqueryable` – Ehsan Sajjad Feb 07 '17 at 14:25

Shyju · Accepted Answer · 2017-02-07T14:24:29.443

1

This line

var query = db.firsttests.Where(a => 1 == 1);

returns an IQueryable collection of firsttest entities. So the type of variable query is IQueryable<firsttest> (because of type inference)

But later you are trying to set a different type (IQueryable<string>)to that variable when you select the Name property.

query = query.Where(a => a.SysId.Equals(SYSID)).Select(a => a.Name);

That is why you are getting this compile error.

Also there is no point in the string null or empty check. long value is not nullable. So you need to have a valid numeric long value for the route to work. So simply use the parameter in your where clause.

[Route("api/FirstTest/{SysId}")]
public List<String> Name(long SysId)
{ 
   using (var db = new AContext())
   {
     return db.firsttests   // here the type is IQueryable<firsttest>
              .Where(a => a.SysId.Equals(SysId)) 
              .Select(a => a.Name) // here the type is IQueryable<string>
              .ToList();  // here the type of List<string>
   }
}

edited Feb 07 '17 at 14:24

answered Feb 07 '17 at 14:20

Shyju

214,206
104
411
497

So the only choice I have is to add `select` after `return` line? is there any explicit conversion method I have to convert explicitly from IQueryable to IQueryable? – LONG Feb 07 '17 at 14:24
You can select and keep it in a variable and return or simply do it in a one line like i did. Eventually the compiler will optimize it and generate code like the one generated by the one liner. – Shyju Feb 07 '17 at 14:25
Your code explains perfectly for me, just curious, no explicit conversion against this line? `query = query.Where(a => a.SysId.Equals(SYSID)).Select( a => a.Name)`, which will convert IQueryable to IQueryable for me? – LONG Feb 07 '17 at 14:29
No. you cannot convert a collection of firsttest to a collection of string like that. you can set the value to a different varibale though (`var nameList=query.Where(a => a.SysId.Equals(SYSID)).Select( a => a.Name)`. Then return that after calling ToList() as your method return type is List. – Shyju Feb 07 '17 at 14:31
1

I see, thanks for your detail answer and explanations ^ ^ – LONG Feb 07 '17 at 14:36

MVC model coding with table model and controller

3 Answers3