How to put the results of a LINQ with join between 2 entities of type T and type T1 into list<T> along with columns from T and T1

See more:

C#

LINQ

Hi,

Kindly excuse. Iam not sure how to frame the question.


I have two tables. Category and Counter.

Category Table :
------------------
code varchar(6)
name varchar(30)
cntrno int

Counter Table :
------------------
code int
name varchar(30)

SQL Statement :
------------------
select category.code, category.name, counter.name as cntname from category join counter on counter.code=category.cntrno order by category.code asc

Output :
-----------------
Code	Name	    Cntname
c001	Breads	    BN001
c002	Cakes	    BN002
c003 	Pizza	    BN001
c004	Pastries    BN002

Im trying to use LINQ to achieve the above. I used this :

List<category> lstcategory = null;
lstcategory = (from caterow in Context.categorys
               join cntrow in Context.counters
               on caterow.cntrno equals cntrow.code
               orderby caterow.code
               select new category{ code = caterow.code, name = caterow.name,  cntname=cntrow.name }).ToList();

In the above query, cntname=cntrow.name won't compile, since cntname is not a column name in class category. How do I get that extra column in the list of type category ?

What I have tried:

Created a class inherited from my category class with the required additional columns as properties locally and changed the list of type category to this class type and casted the output of the LINQ to this type.

New classname = CategoryWithCounterName (with properties code, name, cntname)

List<CategoryWithCounterName> lstcategory = null;
lstcategory = (from caterow in Context.category
               join cntrow in Context.counter
               on caterow.cntrno equals cntrow.code
               orderby caterow.code
               select new CategoryWithCounterName{ code = caterow.code, name = caterow.name,  cntname=cntrow.name }).ToList();

Is this the right way to follow. Kindly advise.