EntitySpaces Using Inner Join

I am currently using Visual Studio 2008, Microsoft SQL 2008 and EntitySpaces 2009.

Usually i am collect data using like this function :

        public TblInspectionRequestDetailCollectionProxyStub FindInspRequestDetailByTestCode(string nameLike)
        {
            TblInspectionRequestDetailCollection coll = new TblInspectionRequestDetailCollection();
            coll.Query.Where(coll.Query.InspTestCode.Like("%" + nameLike + "%")).OrderBy(coll.Query.InspRequestDetailID.Ascending);
            coll.Query.Load();
            return new TblInspectionRequestDetailCollectionProxyStub(coll);
        }

then i try using more table to collect data, but when i try using InnerJoin, i confuse to implementation on my project, i try this code :

        public TblInspectionRequestDetailCollectionProxyStub FindInspRequestDetailByTestCode(string nameLike)
        {
            TblInspectionRequestDetail Detail = new TblInspectionRequestDetail();
            TblInspectionRequestHeader Header = new TblInspectionRequestHeader();
            TblInspectionRequestDetailCollection coll = new TblInspectionRequestDetailCollection();

            /// ????

            return new TblInspectionRequestDetailCollectionProxyStub(coll);
        }

 Did anyone know how to retrieve data from TblInspectionRequestDetail & TblInspectionRequestHeader using InnerJoin ?

 

Regards,

Herry

 

Currently there is not an easy way to do this. Here is a quote from our latest blog post which you should really look at since you are using the proxies.

There are also a few technical issues we must solve yet, though most have been worked out. For example, where do the extra properties go when you bring them back via a join? In the full server side classes this is handled gracefully, and we make those properties appear as if there are in your strongly typed entities via sophisticated binding support. How do we do this on the client side? These are a few of the issues we must solve yet.

One way you could do it for now is to create a "view" perhaps (I haven't tried that though with the proxies) hopefully they don't require a primary key, I can't remember for sure. However, if you were only bringing back a few extra properties for display purposes you could probably manually add properties to the proxies ( and then fill the properties in like such before you return them). It might look something like this pseudocode:

TblInspectionRequestDetailCollectionProxyStub coll = new TblInspectionRequestDetailCollectionProxyStub(coll);
foreach (TblInspectionRequestDetailProxyStub entity in coll)
{
    realEntity = Find the entity in your real join collection - not the proxy collection
    entity.MyFakeProperty = realEntity.GetColumn("realEntity");
}

Does that make sense?

Finally i am using this code :

        public DataTable GetDataInspectionDetail()
        {
            string s = "SELECT " +
            "A.InspRequestID, A.InspRequestNo, A.InspRequestDate, A.InspSection, A.ProjectID, "+
            "B.InspTestCode, B.InspAreaCode, B.DrawingID, B.InspectedDate, B.InspectionStatus, B.InspectionTime, "+
            "B.Description as InspDesc, B.InspRequestDetailID, B.QA, B.QC, B.Remark, B.SubContractor, "+
            "C.Code, C.Description as SectDesc, D.Description as TestDesc, E.Description as AreaDesc, "+
            "F.Title as DrawingTitle, G.EmployeeName, H.ContractorName " +
            "FROM tblInspectionRequestHeader A "+
            "INNER JOIN tblInspectionRequestDetail B ON A.InspRequestID = B.InspRequestID "+
            "INNER JOIN tblMasterInspectionSection C ON A.InspSection = C.SectionID "+
            "INNER JOIN tblMasterInspectionTestCode D ON B.InspTestCode = D.TestCode "+
            "INNER JOIN tblMasterAreaCode E ON B.InspAreaCode = E.Code "+
            "INNER JOIN tblDrawing F ON B.DrawingID = F.DrawingID "+
            "INNER JOIN tblMasterEmployee G ON B.QA = G.EmployeeID "+
            "INNER JOIN tblMasterInspectionContractor H ON B.SubContractor = H.ContractorID";
            return FillDataTable(esQueryType.Text, s);
        }

It is work, but that pure query SQL and make my page more long :(.

Anyway thanks for your advice Mike.

Hi

I don't see anything about your query that you would have difficulty with as it just looks like a number of joins which is easy enough to do - just do them one at a time and you'll see it's straight forward enough - if you're stuck on how then take a look at either the query showcases in the blog entries, or the developer documentation site.

The only potential issue though is that it looks like you're trying to use the proxy stub classes - I think! In your initial post you're using the proxies, but in your last post you're just returning a datatable so it's a little confusing what it is that you want to load/return

Cheers

Martin

Martin is correct, look at our home page example, that kind of query is no problem for EntitySpaces.