Get all data from both table using Linq to sql

Question

I have two tables BIReport and tblFormat. I am using linq to sql in my project.

I want to get data using linq to sql which will be same as the following query.

Select A.*,B.* from Report A inner join tblFormat B on A.ReportId = B.SettingId.

Using above query it will get all data from both table. So How to receive all data from both tables using linq to sql.

Updated :

 <form id="form1" runat="server">
<div>
    <asp:GridView ID="grddata" runat="server"></asp:GridView>

</div>
</form>

Update2:

My query:

 var QInnerJoin1 = (from p in objtest.Reports
                       join p2 in objtest.tblFormats
                       on p.ReportId equals p2.SettingID
                       where p2 != null  
                       select new { p, p2 }).ToList();

    grddata.DataSource = QInnerJoin1;
    grddata.DataBind();

My Error and Data![enter image description here][2]

Solutions:

I have created a class for property which I need to bind to Grid view:

public class TestLinqToSql
{
    public int ReportId { get; set; }
    public string ReportName { get; set; }
    public string FormatName { get; set; }
}

Then I have updated my linq to sql as per below:

  List<TestLinqToSql> objList = (from p in objtest.Reports
                                   join p2 in objtest.tblFormats
                                   on p.ReportId equals p2.SettingID
                                   where p2 != null
                                   select new TestLinqToSql()
                                   {
                                       ReportId = p.ReportId,
                                       ReportName = p.ReportName,
                                       FormatName = p2.FormatName
                                   }).ToList();


    grddata.DataSource = objList1;
    grddata.DataBind();

Its works for me. Thanks.

use this link http://stackoverflow.com/questions/2085422/how-to-do-a-full-outer-join-in-linq — शेखर, Oct 03 '13 at 10:01
it seems that there is no data in you `data source` kindly check you have data in the query result or not. — शेखर, Oct 04 '13 at 07:42
Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). — John Saunders, Oct 04 '13 at 12:17

Andrew · Answer 1 · 2013-10-04T12:15:51.400

0

I think the error you are getting is because you are trying to join result (a local memory object) with categories, which is a database object. In order to resolve this you'll need to use Contains on your local results object and remove the join between result and categories. Then, just before your select you will have:

// Your old code here
where result.Contains (sc.ID) // new code here    
select new { sc, st }).ToList();

UPDATE

Actually, looking at your requirements vs. the code you've got then would something like the following not work?

var innerJoinQuery =
    (from A in Reports 
    join B in ChartSetting on A.ReportId  equals B.ReportId          
    select new  { A, B }).ToList();

That is syntactically the same as

Select A.*,B.* from Reports A inner join ChartSetting B on A.ReportId = B.ReportId

A lot of the code you've got in your new query seems to be a bit obsolete - including the creating of result.

Update 2

The list of items you posted has the ChartSetting value to be NULL, so if you really can't change the database then try the following:

var innerJoinQuery =
    (from A in Reports 
    join B in ChartSetting on A.ReportId  equals B.ReportId 
    where B != null  
    select new  { A, B }).ToList();

edited Oct 04 '13 at 12:15

answered Oct 04 '13 at 07:43

Andrew

2,225
3
22
41

HI Andrew Thanks for ur reply but still getting error as I have mentioned my above comment. – Hitesh Oct 04 '13 at 11:39
If you put a breakpoint on grddata.DataBind(); then what does the debugged say the value of qtestt is? – Andrew Oct 04 '13 at 11:43
Plese see my update above. I think the problem is in your item #6 which is NULL. The code above should sort it. – Andrew Oct 04 '13 at 11:49
Ok. That definitely should have worked based on your SQL criteria. Can you check your database and ensure there is a foreign key relationship between Reports.ReportId and ChartSetting.ReportID and also that there are no NULL values in either of these fields? – Andrew Oct 04 '13 at 12:04
I have checked all those thing which u mentioned in above comment but still not working. It gives error. – Hitesh Oct 04 '13 at 12:18
Have you tried the code in Update 2? Can you please post the trace from the breakpoint again (if it is different?) – Andrew Oct 04 '13 at 12:19
I have update my question with all requirement information. See update2 in my edited questions. – Hitesh Oct 04 '13 at 12:31
What happened to Reports and ChartSettings, where did these new tables come from? – Andrew Oct 04 '13 at 12:40
This was name given by me to ask question here. U can consider BiReports as Report and tbl format as ChartSettings. This is my actual code and query and dbml file. – Hitesh Oct 04 '13 at 12:45
Ok, last idea and then I'm out. Have you confirmed AutoGenerateColumns in grddata is set to True? – Andrew Oct 04 '13 at 12:52
Yes.I have also check with AutogenerateComuns for true and false but in both cases it is not working. thanks for your support. – Hitesh Oct 04 '13 at 12:55
If you solve it I'd be interested to know how because it seems illogical that it isn't working now! – Andrew Oct 04 '13 at 13:01
hi Andrew I have find other way to solve it. I have updated my question and edit solutions. – Hitesh Oct 04 '13 at 13:19

Get all data from both table using Linq to sql

1 Answers1

UPDATE

Update 2